CODEWITHSUNDEEP
sqlsql-server
Andurit
Andurit

Reputation: 5762

MS SQL last record

I just try to write MS SQL statement to get LAST DATA from database, unfortunately when i add there DESC LIMIT 1 it says wrong syntax despite the fact it looks OK to me.

Can somebody with more skill look at it?

Select
  sum(spareparts), 
  month(calculationdate) 
from cz_axnmrs_calculations 
where CASE_ID in (select case_id 
                  from cz_axnmrs_cases 
                  where insurer_memberid = 'MM-O-5B57274F') 
and YEAR(calculationdate)='2014'  
group by month(calculationdate) DESC LIMIT 1

LIKE THIS it work:

Select 
  sum(spareparts), 
  month(calculationdate) 
from cz_axnmrs_calculations 
where CASE_ID in (select case_id 
                  from cz_axnmrs_cases 
                  where insurer_memberid = 'MM-O-5B57274F') 
and YEAR(calculationdate)='2014'  
group by month(calculationdate)

Upvotes: 1

Views: 129

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

In SQL Server 2012+, Microsoft supports the ANSI standard OFFSET clause. You can write this as:

Select sum(spareparts), month(calculationdate)
from cz_axnmrs_calculations
where CASE_ID in (select case_id from cz_axnmrs_cases where insurer_memberid = 'MM-O-5B57274F') and
      YEAR(calculationdate)='2014'
group by month(calculationdate) DESC
fetch first 1 row only;

Upvotes: 2

Matt
Matt

Reputation: 15061

Select TOP 1 sum(spareparts), month(calculationdate) from cz_axnmrs_calculations 
WHERE CASE_ID in (select case_id 
FROM cz_axnmrs_cases WHERE insurer_memberid = 'MM-O-5B57274F') AND YEAR(calculationdate)='2014'  
GROUP BY month(calculationdate)

Upvotes: 1

crthompson
crthompson

Reputation: 15875

SQL server uses TOP instead of LIMIT to restrict number of records.

Your query becomes:

Select top 1 
  sum(spareparts), 
  month(calculationdate) 
from cz_axnmrs_calculations 
where CASE_ID in (select case_id 
                  from cz_axnmrs_cases 
                  where insurer_memberid = 'MM-O-5B57274F') 
and YEAR(calculationdate)='2014'  
group by month(calculationdate) DESC

Upvotes: 4

Related Questions