yyx748 Posted June 15, 2009 Share Posted June 15, 2009 How do I write this SQL statement such that i could get that output? "Getting the category of the latest updated news." ==SQL Table== ID CATEGORY 76 Sports 75 Sports 74 Entertainment 73 Sports 72 Business 71 World 70 Business ==OUTPUT== Sports Entertainment Business World There are still some more other data in the SQL Table such as news title, date etc. I just need the sql statement for "Getting the category of the latest updated news." Mine was "Select distinct last(cat) order by ID desc" but it couldn't work. I added the LAST() was because if not it will look like this ==SQL Table== 76 Sports . . . 6 Sports 5 Entertainment 4 Sports 3 Business 2 World 1 Business ==OUTPUT== Entertainment Sports World Business Any kind soul to help me?? thanks!! Quote Link to comment Share on other sites More sharing options...
kickstart Posted June 15, 2009 Share Posted June 15, 2009 Hi Something like this:- Select CATEGORY FROM newtable order by ID desc LIMIT 0,1 All the best Keith Quote Link to comment Share on other sites More sharing options...
yyx748 Posted June 15, 2009 Author Share Posted June 15, 2009 Nope, aint working this is my sql table IDSportsName 10SoccerJohn 9BasketballAlan 8SoccerDean 7BaseballCalvin 6RugbyTosh 5GolfTobey 4BaseballPeter 3SoccerMicheal 2GolfPatrick 1BaseballLuke I wanna get the 5 latest Sports that the new members have chosen which should be... 1. Soccer 2. BasketBall 3. Baseball 4. Rugby 5. Golf Select CATEGORY FROM newtable order by ID desc LIMIT 0,1 Will get... 1. Soccer 2. Basketball 3. Soccer 4. Baseball 5. Rugby 6. Golf 7. Baseball 8. Soccer 9. Golf 10. Baseball Quote Link to comment Share on other sites More sharing options...
Maq Posted June 15, 2009 Share Posted June 15, 2009 You should really TIMESTAMP your entries. There's no reason not to... Assuming the greatest 5 ID's would give you the latest 5 entries: Select Sports FROM table GROUP BY Sports ORDER BY ID DESC LIMIT 5 Quote Link to comment Share on other sites More sharing options...
yyx748 Posted June 15, 2009 Author Share Posted June 15, 2009 You should really TIMESTAMP your entries. There's no reason not to... Assuming the greatest 5 ID's would give you the latest 5 entries: Select Sports FROM table GROUP BY Sports ORDER BY ID DESC LIMIT 5 That would result in duplicate output 1. Soccer 2. Basketball 3. Soccer 4. Baseball 5. Rugby Quote Link to comment Share on other sites More sharing options...
Maq Posted June 15, 2009 Share Posted June 15, 2009 Are you sure? Quote Link to comment Share on other sites More sharing options...
kickstart Posted June 16, 2009 Share Posted June 16, 2009 Hi I wanna get the 5 latest Sports that the new members have chosen which should be... Right, so you don't want duplicates. As such this should give you the latest 5 :- SELECT Sports, Max(id) As MaxId FROM `SportsTable` GROUP BY Sports ORDER BY MaxId DESC LIMIT 0,5 All the best Keith Quote Link to comment Share on other sites More sharing options...
Maq Posted June 16, 2009 Share Posted June 16, 2009 Isn't that essentially the same query I gave him? Quote Link to comment Share on other sites More sharing options...
kickstart Posted June 16, 2009 Share Posted June 16, 2009 Isn't that essentially the same query I gave him? Yes, although without getting the max id the order could well be pretty random. Cannot see why your query would bring back duplicates though. All the best Keith Quote Link to comment Share on other sites More sharing options...
Maq Posted June 16, 2009 Share Posted June 16, 2009 I see, but doesn't the ORDER BY and GROUP BY take care of that? My logic is that if you're ordering by 'ID DESC', which orders the list greatest to least by ID, and 'GROUP BY Sport', which would rid duplicates, then you wouldn't need to use MAX() because these 2 clauses assume that. Quote Link to comment Share on other sites More sharing options...
kickstart Posted June 16, 2009 Share Posted June 16, 2009 Hi My understanding is that the GROUP BY will return one record per grouped value, with the other values being from an effectively random row with that value. The order by clause will be after the group by, hence will work on what is left after the group by has finished (ie, order on the randomly selected ID field). All the best Keith Quote Link to comment Share on other sites More sharing options...
yyx748 Posted June 17, 2009 Author Share Posted June 17, 2009 This is the answer i had been looking for select category from ( select category, max(id) mk from YourTable group by category ) x order by mk desc Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.