CODEWITHSUNDEEP
sql-server-2005selectdistinct
user213559
user213559

Reputation:

sql select statement based on distinct column values

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

Upvotes: 1

Views: 1654

Answers (4)

marc_s
marc_s

Reputation: 754488

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.

Upvotes: 1

Joe Daley
Joe Daley

Reputation: 46456

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
)

Upvotes: 1

jake.cook1
jake.cook1

Reputation: 51

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

Upvotes: 0

adopilot
adopilot

Reputation: 4500

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

Upvotes: 1

Related Questions