SQL Query

Question

SQL Query

bhakti-sonu

Mr or Mrs. 500

Points: 556
More actions
January 19, 2010 at 12:29 am

#100048

Table ABC contains Columns vehicle_name,drivers.
Records in table ABC are:-
vehicle_name Drivers
Car
Car 2
Car 2,3
Car 2,5,8
Scooter
Scooter 4
Scooter 4,6
Scooter
I want result in table PQR like below [ Without Cursor]
e.g.REsult for above records should be:-
Table PQR
vehicle_name Drivers
Car 2,5,8
Scooter 4,6
i.e. distinct vehicle_name from table ABC into Table PQR and value of MAX length drivers from PQR to ABC for appropritevehicle
I have tried following query but it doesn't work properly:-
Current Query
Select distinct(vehicle_name),drivers from (SELECT vehicle_name, drivers from tblresult group by vehicle_name, drivers having len(drivers)=max(len(drivers)) and len(drivers)>2) group by vehicle_name, drivers

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://qa.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

arun.sas SSChampion Points: 11831 More actions · Answer 2

bhakti-795560 (1/19/2010)
distinct vehicle_name from table ABC into Table PQR and value of MAX length drivers from PQR to ABC for appropritevehicle
Current Query
Select distinct(vehicle_name),drivers from (SELECT vehicle_name, drivers from tblresult group by vehicle_name, drivers having len(drivers)=max(len(drivers)) and len(drivers)>2) group by vehicle_name, drivers

Hi,

From your statement, you need the maximum length of the driver from the table, so why you write the statement like and len(drivers)>2?,

And with this ,may the maximum length of the drivers selected

select vehicle_name,max(Drivers) from ABC

group by vehicle_name

bhakti-sonu Mr or Mrs. 500 Points: 556 More actions · Answer 3

bhakti-sonu

Mr or Mrs. 500

Points: 556

January 19, 2010 at 2:39 am

#1105289

Thanks for your Answer, its working fine.

arun.sas SSChampion Points: 11831 More actions · Answer 4

Hi,

In fact it’s a fun, I asked, why you put the len(drivers)>2 in your select statement, but you says the normal group by/ select is enough. However thanks!!!