CODEWITHSUNDEEP
sql-serversql-server-2008
Georgep2000
Georgep2000

Reputation: 39

Using max(col) with count in sub-query SQL Server

I am putting together a query in SQL Server but having issues with the sub-query I wish to use the max(loadid) and count the number of records the query returns.

So for example my last loadid is 400 and the amount of records with 400 is 2300, so I would my recor_count column should display 2300. I have tried various ways below but am getting errors. 

select count (loadid) 
from t1 
where loadid = (select max(loadid) from t1) record_count;

(select top 1 LOADID, count(*) 
 from t1
 group by loadid
 order by count(*) desc) as Record_Count

Upvotes: 1

Views: 87

Answers (3)

Mihir Shah
Mihir Shah

Reputation: 988

Another simplest way to achieve the result :

Set Nocount On;

Declare @Test Table
(
    Id      Int
)

Insert Into @Test(Id) Values
(397),(398),(399),(400)

Declare @Abc Table
(
     Id     Int
    ,Value  Varchar(100)
)

INsert Into @Abc(Id,Value) Values
 (398,'')
,(400,'')
,(397,'')
,(400,'')
,(400,'')

Select   a.Id
        ,Count(a.Value) As RecordCount
From    @Abc As a
        Join
        (
            Select  Max(t.Id) As Id
            From    @Test As t
        ) As v On a.Id = v.Id
Group By a.Id

Upvotes: 0

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17157

Showing loadid and number of matching rows with the use of grouping, ordering by count and limiting the output to 1 row with top.

select top 1 loadid, count(*) as cnt
from t1
group by loadid
order by cnt desc

Upvotes: 1

Mureinik
Mureinik

Reputation: 311438

This may be easier to achieve with a window function in the inner query:

SELECT COUNT(*)
FROM   (SELECT RANK() OVER (ORDER BY loadid DESC) AS rk
        FROM   t1) t
WHERE  rk = 1

Upvotes: 0

Related Questions