Prev: Depending on input parameters, modify select statement in SP
Next: Data error. Cyclic redundancy check
From: alexandre.lemiere on 7 Apr 2010 09:07
Hi,
Here is the DDL :

CREATE TABLE traffic (
unique_id int NOT NULL PRIMARY KEY
, train_number int NOT NULL
, carrier_id int NOT NULL
);

-- some test data
INSERT INTO traffic (unique_id, train_number, carrier_id) VALUES (1,
11, 1);
INSERT INTO traffic (unique_id, train_number, carrier_id) VALUES (2,
11, 1);
INSERT INTO traffic (unique_id, train_number, carrier_id) VALUES (3,
11, 2);
INSERT INTO traffic (unique_id, train_number, carrier_id) VALUES (4,
22, 1);
INSERT INTO traffic (unique_id, train_number, carrier_id) VALUES (5,
22, 2);


I'm using MSSQL 2000, I would prefer a "generic" SQL ANSI/ISO query.

I need to find the carrier_id which is the most used for a given
train_number.
ie : for train_number 11, the most used carrier_id is 1 (used two
times).

Here's what I can get at the moment, but I guess there are nicer way
to do it.

SELECT /* TOP 1 */ TRAF.carrier_id
FROM traffic TRAF
WHERE TRAF.train_number = 11
GROUP BY TRAF.carrier_id
HAVING COUNT(TRAF.carrier_id) =
( SELECT MAX(T2.carrier_id_cnt)
FROM (
SELECT TRAF1.carrier_id
, COUNT(TRAF1.carrier_id) AS carrier_id_cnt
FROM traffic TRAF1
WHERE TRAF1.train_number = 11 -- TRAF.train_number
GROUP BY TRAF1.carrier_id
) T2
)
;

I can't use TRAF.train_number in the innermost query (see comment),
MSSQL 2k says "internal error".
Do you know why ?

In case there are two (or more) equal usage (see train_number 22 which
is used 1 time with carrier_id 1 and 1 time with carrier_id 2)
, I want only 1 carrier_id, any one (is there any other way than
using TOP 1 here ?).
From: Uri Dimant on 7 Apr 2010 10:04
SELECT TOP 1 train_number,

carrier_id,COUNT(carrier_id)as cnt

FROM traffic

WHERE train_number=11

GROUP BY train_number,carrier_id

ORDER BY cnt DESC





<alexandre.lemiere(a)gmail.com> wrote in message
news:73fb3289-e618-4258-9b44-13a4e5831c94(a)o30g2000yqb.googlegroups.com...
> Hi,
> Here is the DDL :
>
> CREATE TABLE traffic (
> unique_id int NOT NULL PRIMARY KEY
> , train_number int NOT NULL
> , carrier_id int NOT NULL
> );
>
> -- some test data
> INSERT INTO traffic (unique_id, train_number, carrier_id) VALUES (1,
> 11, 1);
> INSERT INTO traffic (unique_id, train_number, carrier_id) VALUES (2,
> 11, 1);
> INSERT INTO traffic (unique_id, train_number, carrier_id) VALUES (3,
> 11, 2);
> INSERT INTO traffic (unique_id, train_number, carrier_id) VALUES (4,
> 22, 1);
> INSERT INTO traffic (unique_id, train_number, carrier_id) VALUES (5,
> 22, 2);
>
>
> I'm using MSSQL 2000, I would prefer a "generic" SQL ANSI/ISO query.
>
> I need to find the carrier_id which is the most used for a given
> train_number.
> ie : for train_number 11, the most used carrier_id is 1 (used two
> times).
>
> Here's what I can get at the moment, but I guess there are nicer way
> to do it.
>
> SELECT /* TOP 1 */ TRAF.carrier_id
> FROM traffic TRAF
> WHERE TRAF.train_number = 11
> GROUP BY TRAF.carrier_id
> HAVING COUNT(TRAF.carrier_id) =
> ( SELECT MAX(T2.carrier_id_cnt)
> FROM (
> SELECT TRAF1.carrier_id
> , COUNT(TRAF1.carrier_id) AS carrier_id_cnt
> FROM traffic TRAF1
> WHERE TRAF1.train_number = 11 -- TRAF.train_number
> GROUP BY TRAF1.carrier_id
> ) T2
> )
> ;
>
> I can't use TRAF.train_number in the innermost query (see comment),
> MSSQL 2k says "internal error".
> Do you know why ?
>
> In case there are two (or more) equal usage (see train_number 22 which
> is used 1 time with carrier_id 1 and 1 time with carrier_id 2)
> , I want only 1 carrier_id, any one (is there any other way than
> using TOP 1 here ?).


From: Plamen Ratchev on 7 Apr 2010 10:36
You can add the train_number column to GROUP BY (you pull only one train_number, so it doesn't matter), that way you can
use it in the HAVING clause:

SELECT carrier_id
FROM traffic AS A
WHERE train_number = 11
GROUP BY carrier_id, train_number
HAVING COUNT(*) = (SELECT MAX(cnt)
FROM (SELECT COUNT(*) AS cnt
FROM traffic AS B
WHERE B.train_number = A.train_number
GROUP BY B.carrier_id) AS T);

--
Plamen Ratchev
http://www.SQLStudio.com
From: Uri Dimant on 7 Apr 2010 11:05
Hi Plamen
I was thinking about the same logic but since we have a WHERE clause how
does your query differ from mine? What I missed?



"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:n9CdnSuU3aTuBSHWnZ2dnUVZ_sUAAAAA(a)speakeasy.net...
> You can add the train_number column to GROUP BY (you pull only one
> train_number, so it doesn't matter), that way you can use it in the HAVING
> clause:
>
> SELECT carrier_id
> FROM traffic AS A
> WHERE train_number = 11
> GROUP BY carrier_id, train_number
> HAVING COUNT(*) = (SELECT MAX(cnt)
> FROM (SELECT COUNT(*) AS cnt
> FROM traffic AS B
> WHERE B.train_number = A.train_number
> GROUP BY B.carrier_id) AS T);
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


From: Plamen Ratchev on 7 Apr 2010 11:29
Hi Uri,

The request was to avoid using the TOP option and to write the query in ANSI SQL. This is why I did the rewrite with
subquery.

--
Plamen Ratchev
http://www.SQLStudio.com
 |  Next  |  Last
Pages: 1 2
Prev: Depending on input parameters, modify select statement in SP
Next: Data error. Cyclic redundancy check