Hi friends,

I got a table as follows

Name | idNo | started | type | entrytype
-----------------------------------------------------------------
Nick | 12 | 12-02-2011 | 0 | 1
Sam | 14 | 13-02-2011 | 1 | 1
Paul | 12 | 14-02-2011 | 1 | 1
Jwal | 12 | 13-02-2011 | 1 | 1
------------------------------------------------------------------

I want to select using "DISTINCT" I wrote a query as follows

select distinct idNo from table where entrytype='1' order by started LIMIT 0,10

Working but I is not sorting the values according to "started" ( I mean by date )

The following query will sorting the data according to date ..
But I need distinct values .. Please help

select * from table where entrytype='1' order by started LIMIT 0,10

Thanks in advance
Rajeesh

Recommended Answers

All 2 Replies

Your problem is bad database design. By which value would you like the aggregate row with idNo 12 to be sorted - 12, 13, or 14/02/2011?
You cannot have distinct rows as a GROUP BY result and at the same time retrieve the non-grouped values separately. The non-grouped values disappear in any query result with aggregate functions. MySQL does a bad job here by displaying randomly just the first non-aggregate field content it encounters.

commented: agree +9

try this ordering

SELECT DISTINCT idNo FROM table WHERE entrytype='1' ORDER BY started asc, started LIMIT 0,10

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.