select statement group by
-
Hi I have table Table1 contains SerialNumber, Name, City ,State SerialNumber, Name, City , State 1 ABC Dallas TX 2 BCD VCDFV NY 3 ABC Dallas TX Here I want to get the max of the serialnumber with if same combination of Name, City ,State is repeated , here 1 and 3 repeted , Please suggest how can I do this Thanks,
-
Hi I have table Table1 contains SerialNumber, Name, City ,State SerialNumber, Name, City , State 1 ABC Dallas TX 2 BCD VCDFV NY 3 ABC Dallas TX Here I want to get the max of the serialnumber with if same combination of Name, City ,State is repeated , here 1 and 3 repeted , Please suggest how can I do this Thanks,
select max(serialnumber), Name, City, State
from YourTable
group by Name, City, StateThat's a start. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
Hi I have table Table1 contains SerialNumber, Name, City ,State SerialNumber, Name, City , State 1 ABC Dallas TX 2 BCD VCDFV NY 3 ABC Dallas TX Here I want to get the max of the serialnumber with if same combination of Name, City ,State is repeated , here 1 and 3 repeted , Please suggest how can I do this Thanks,
Building on Chris's post: If you need only max by state you might try something like:
select yourtable.serialnumber, yourtable.name, yourtable.city, yourtable.state
from yourtable
Inner join (
select Max(serialnumber), state
from yourtable
group by state) as sub1
on yourtable.serialnumber = sub1.serialnumber(NOT TESTED)