CODEWITHSUNDEEP
t-sqlsql-server-2012
ADO_kg
ADO_kg

Reputation: 65

Select values by date

I have same data structure as Select only last value of date? , but i need to get values for each date, I marked them. The solution for Feroc's question is giving last date only.

localName Date_Time                 RH  
BAG012    2014-10-09 17:17:58.000   16  <--     
BAG012    2014-10-09 17:13:28.000   16  
BAG012    2014-10-09 17:12:23.000   16  
BAG012    2014-10-09 16:52:54.000   16  
BAG012    2014-10-08 05:14:56.000   16  <--         
BAG012    2014-10-08 04:45:31.000   16  
BAG012    2014-10-08 04:44:08.000   16  
SAG165    2014-10-28 11:22:14.000   698 <--         
SAG165    2014-10-28 11:09:14.000   698     
SAG165    2014-10-28 10:53:18.000   698     
SAG165    2014-10-27 19:30:14.000   693 <--         
SAG165    2014-10-27 19:14:51.000   693     
SAG165    2014-10-27 19:13:56.000   693

Here is a code I am using:

WITH CTE AS
(
   SELECT LTRIM(localName) as localName, CAST(year(Date_Time) as varchar)+'-'+CAST(month(Date_Time) as varchar)+'-'+CAST(day(Date_Time) as varchar) as date_time, RH, tank, mode,
     RN = ROW_NUMBER() OVER (PARTITION BY localName ORDER BY Date_Time DESC)
   FROM dbo.SMCData
)

SELECT DISTINCT Date_Time, localName, RH, tank, mode
FROM CTE
WHERE RN = 1 
order by LocalName asc
  1. as the datetime includes time also, I tried to remove time and leave only date.
  2. Tried to used DISTINCT Date_Time

both above cases are not making any changes in result.

ANSWER: thanks for all, the following worked for me:

WITH CTE AS
(
   SELECT localName, Date_Time, RH,
     RN = ROW_NUMBER() OVER (PARTITION BY localName, CAST(Date_Time AS date) ORDER BY Date_Time DESC)
   FROM dbo.TableName
)
SELECT localName, Date_Time, RH
FROM CTE
WHERE RN = 1
ORDER BY localName, Date_Time;

Upvotes: 0

Views: 49

Answers (4)

richg
richg

Reputation: 1

SELECT DISTINCT localName, Date , RH
FROM table_name

This should give unique date and RH values and corresponding localName.

Upvotes: 0

Bruce Dunwiddie
Bruce Dunwiddie

Reputation: 2908

WITH CTE AS
(
   SELECT localName, Date_Time, RH,
     RN = ROW_NUMBER() OVER (PARTITION BY localName, CAST(Date_Time AS date) ORDER BY Date_Time DESC)
   FROM dbo.TableName
)
SELECT localName, Date_Time, RH
FROM CTE
WHERE RN = 1
ORDER BY localName, Date_Time;

Upvotes: 1

jpw
jpw

Reputation: 44931

Group by the date also:

SELECT localName, MAX(date_time) AS max_date_time, MAX(rh) AS max_rh
FROM your_table
GROUP BY localName, CAST(date_time AS date)

This will give you the last date for each localname.

Sample SQL Fiddle

Output:

localName  max_date_time           max_rh
---------- ----------------------- -----------
BAG012     2014-10-08 05:14:56.000 16
BAG012     2014-10-09 17:17:58.000 16
SAG165     2014-10-27 19:30:14.000 693
SAG165     2014-10-28 11:22:14.000 698

Upvotes: 0

itay
itay

Reputation: 357

Use select distinct:

SELECT DISTINCT date
FROM table_name

It will give you on of each date

Upvotes: 0

Related Questions