Reputation: 65
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
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
Reputation: 1
SELECT DISTINCT localName, Date , RH
FROM table_name
This should give unique date and RH values and corresponding localName.
Upvotes: 0
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
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.
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
Reputation: 357
Use select distinct:
SELECT DISTINCT date
FROM table_name
It will give you on of each date
Upvotes: 0