select query to get first row from rows having multiple id's.(without partition by)

Question

id   date         amount      documentNo     paperID
1    2015/10/15   500         1234             34
1    2015/10/15   100         1332             33
2    2015/10/13   200         1302             21
2    2015/10/13   400         1332             33
3    2015/11/23   500         1332             43

I should get the output as:

id        date      amount       documentNo     paperID
1       2015/10/15   500         1234             34
2       2015/10/13   200         1302             21
3       2015/11/23   500         1332             43

Please suggest a simple select query to fetch only one row without partition by. Note: the date remain same for a particular id.

3 questions: 1) What have you tried so far? 2) Why are you imposing a restriction on using window functions 3) What do you consider the 1st row (because database rows are unordered unless you specify a specific order based on column values)? — sstan, Jun 04 '16 at 03:19
1) i am not able to figure out a way. 2) window functions do not function on my server 3) if I can get any one of the row with same id, i am fine with it. — Saif ali Karedia, Jun 04 '16 at 03:21
what is your database server? You have sql server tagged, that should be able to handle window functions just fine — sstan, Jun 04 '16 at 03:22
of course you can. Did you try it? If it doesn't work, that would make for an interesting question. — sstan, Jun 04 '16 at 03:28
are you using `SQL Server` or `MySQL` ? you have both tagged — Squirrel, Jun 04 '16 at 03:59

score 2 · Answer 1 · edited May 23 '17 at 10:29

Try a null-self-join. Basically you are comparing each row to some other version of that row ,but, via an inequality (here I have used documentNo) you end-up with a single row that has no match.

See this SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE Table1
    (`id` int, `date` datetime, `amount` int, `documentNo` int, `paperID` int)
;

INSERT INTO Table1
    (`id`, `date`, `amount`, `documentNo`, `paperID`)
VALUES
    (1, '2015-10-15 00:00:00', 500, 1234, 34),
    (1, '2015-10-15 00:00:00', 100, 1332, 33),
    (2, '2015-10-13 00:00:00', 200, 1302, 21),
    (2, '2015-10-13 00:00:00', 400, 1332, 33),
    (3, '2015-11-23 00:00:00', 500, 1332, 43)
;

Query 1:

SELECT
      t1.*
FROM table1 AS t1
      LEFT OUTER JOIN table1 AS t2 ON t1.id = t2.id
                  AND t1.date = t2.date
                  AND t2.documentNo < t1.documentNo
WHERE t2.ID IS NULL

Results:

| id |                       date | amount | documentNo | paperID |
|----|----------------------------|--------|------------|---------|
|  1 |  October, 15 2015 00:00:00 |    500 |       1234 |      34 |
|  2 |  October, 13 2015 00:00:00 |    200 |       1302 |      21 |
|  3 | November, 23 2015 00:00:00 |    500 |       1332 |      43 |

EDIT: There are several approaches to this problem even without windowing functions such as row_number() , here is a previous answer covering some MySQL specific alternatives.

Added link to previous question. – Paul Maxwell Jun 04 '16 at 07:39 — Paul Maxwell, Jun 04 '16 at 07:39

select query to get first row from rows having multiple id's.(without partition by)

1 Answers1