2

Possible Duplicate:
SQL Server: Only last entry in GROUP BY

I have a table like this:

id| name  | attendence
1 | Naveed| 1
2 | Naveed| 1
3 | Adil  | 1
4 | Adil  | 1

I use following query:

SELECT * FROM `test` WHERE `attendence`=1 GROUP BY name

The result with above query:

id| name   | attendence
3 | Adil   | 1
1 | Naveed | 1

Question:

Above result group rows by name but show first row from each group. I want to select last row(by id) from each group.

For example:

id| name   | attendence
2 | Naveed | 1
4 | Adil   | 1

How to write query for above result.

Thanks

Community
  • 1
  • 1
Naveed
  • 41,517
  • 32
  • 98
  • 131
  • Doesn't `SELECT * FROM test WHERE attendence = 1 GROUP BY name` throw an error?? – egrunin Aug 10 '10 at 14:48
  • @egrunin: Assuming that NAVEED is using MySQL: http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html – Peter Lang Aug 10 '10 at 15:05
  • http://php.azerot.com/sql-group-by-get-last-record-from-each-group/ here is the same problem for your question's answer – syraz37 Mar 30 '12 at 07:21

4 Answers4

9
SELECT a.* 
FROM test a 
WHERE a.attendence = 1 
AND NOT EXISTS (select 1 from test where name = a.name and id > a.id and attendence = 1)
GROUP BY name

Might not even need the group by anymore.

Fosco
  • 38,138
  • 7
  • 87
  • 101
4
SELECT MAX("id"), "name" FROM "test" WHERE "attendence" = 1 GROUP BY "name"
aib
  • 45,516
  • 10
  • 73
  • 79
1
SELECT Name, Max(ID) FROM table WHERE attendance = 1 GROUP BY Name
bluish
  • 26,356
  • 27
  • 122
  • 180
Alex M
  • 11
  • 1
0

Use the following query:

SELECT * FROM `test` WHERE `attendence`=1 GROUP BY name ORDER BY `id` DESC LIMIT 1

That will only select the row that meets the criteria with the highest id.

Joseph
  • 1,988
  • 14
  • 21
  • It is giving me only one record. Order By is applied after Group By here. – Naveed Aug 10 '10 at 14:12
  • Yes, it is only giving you one record. That is what the LIMIT 1 is for. You stated you only wanted the last row. This will give you only the last row based on the id. If you still want all the rows returned, take off the phrase `LIMIT 1`. – Joseph Aug 10 '10 at 14:16
  • You did not understand my question. I want to get all groups but each group should be represented by last row of that group. – Naveed Aug 10 '10 at 14:18
  • You are correct. I did misunderstand the question. `Fosco` appears to have the best answer imo. – Joseph Aug 10 '10 at 14:39