sql select statement based on distinct column values

1
votes

select distinct * from Drivers where IsDeleted=0 gives me this,

alt text http://www.imagechicken.com/uploads/1274691753099236100.jpg

I want select * based on distinct DriverMobNo column

4
sql-server-2005selectdistinct
"I want select * based on distinct DriverMobNo column" - not sure what you mean. Can you give an example of the results table that you want to get? - Joe Daley
@joe i want to remove a row which contains DriverMobNo in the previous rows... - user213559
Do you mean you want it to return only one driver for each DriverMobNo? So Chandru and Uday1 would not be returned, because you already have a driver (Uday) for that DriverMobNo? - Joe Daley

4 Answers

1
votes

Given the fact you have three rows with identical DriverMobNo - which one of those three do you want when doing your SELECT ?? An arbitrary one? The most recent one? The oldest one??

You can certainly do a

SELECT DISTINCT DriverMobNo FROM dbo.Drivers WHERE IsDeleted = 0

and get those distinct DriverMobNo values - but if you want to have all the columns in your SELECT, you need to be more specific as to which of the three rows with DriverMobNo = 9566643707 you want to retrieve.

UPDATE: Ok, you want to oldest one - one way to do this is using a CTE (Common Table Expression):

WITH Drivers AS
(
    SELECT 
        DriverId, DriverName, DriverMobNo, CreatedDate,
        ROW_NUMBER() OVER (PARTITION BY DriverMobNo ORDER BY CreatedDate) 'RowNo'
    FROM dbo.Drivers 
    WHERE IsDeleted = 0
) 
SELECT DriverId, DriverName, DriverMobNo, CreatedDate
FROM Drivers
WHERE RowNo = 1

That should "partition" your data by DriverMobNo and start counting up, order by creation date, so the RowNo=1 entry will always be the oldest for each DriverMobNo.

1
votes
select * from Drivers  where DriverMobNo in (select DriverMobNo from Drivers where IsDeleted=0
 group by DriverMobNo ) and IsDeleted=0
1
votes

This will select one driver for each DriverMobNo. If there is more than one driver for a particular DriverMobNo, it returns the first one.

select * from Drivers
where DriverId in (
  select min(DriverId) from Drivers
  where IsDeleted = 0
  group by DriverMobNo
)
0
votes

It a bit hard to understand what you want there.

It is because you have unique rows there you would have to use the coloumns you need.

For Example

SELECT DISTINCT DriveMobNo, CreatedDate FROM Drivers WHERE IsDeleted = 0

would give you the unique drivers number and the created date but you would still have rows that will come back with the same mobile number twice as there a different dates there to