sql select statement based on distinct column values....

Question

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

I want select * based on distinct DriverMobNo column

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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
)