CODEWITHSUNDEEP
sqlt-sql
highwingers
highwingers

Reputation: 1669

Top 1 Row with Count

I have a Query with the following output

SELECT  BIN_NAME,ROW_NUMBER() Over(Order by BIN_NAME) C
FROM USR_BINS_ITEMS I 
LEFT JOIN USR_BINS B ON I.BIN_COD=B.BIN_COD 
WHERE I.LOC_ID='6' 
And I.ITEM_NO = '364001'

And the output is....

Bin     c
--------------
E1-03   1    
E1-08   2    
E2-01   3

However I would like my output to be 

E1-03   ,  3

Which means, Select FIRST Row Bin Value only BUT show total row Count (or Max value from column C) as well. I am using MS SQL Server 2008.

Upvotes: 1

Views: 1494

Answers (2)

cdhowie
cdhowie

Reputation: 168988

Try this:

SELECT TOP 1 BIN_NAME, X.TOTAL
FROM USR_BINS_ITEMS I

LEFT JOIN USR_BINS B
ON I.BIN_COD = B.BIN_COD

INNER JOIN (
    SELECT COUNT(*) AS TOTAL
    FROM USER_BINS_ITEMS I2
    WHERE I2.LOC_ID = '6' And I2.ITEM_NO = '364001'
) X ON TRUE

WHERE I.LOC_ID = '6' And I.ITEM_NO = '364001';

It requires that the search be performed twice, but it should be more performant than using a subquery (SELECT BIN_NAME, (SELECT COUNT(*) ...) AS TOTAL ...) assuming that the query planner is not smart enough to optimize that case.

Either way, this query should perform quite well and meet your requirements.

Note that without an ORDER BY clause, the database engine is free to return to you any row it wishes from the result set. Add an ORDER BY clause if you want to get some specific row.

Upvotes: 1

GilM
GilM

Reputation: 3761

Do you just want:

SELECT MIN(BIN_NAME) as BIN_NAME, COUNT(*) AS BIN_COUNT
FROM ...

Upvotes: 3

Related Questions