• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

Select Top SQL Query

Hello,

I have a table named READING this table contains POINT_DETAIL_ID , VALUE and TAKEN fields. This table is used to store measurements taken on a point. Our consultants have asked for a report which lists the POINT_DETIAL_ID and the last two VALUES asscoitated with the Point Detail.

Does anybody have an idea if it is possible using SQL and if so how.

Many many thanks.
0
mimicuser
Asked:
mimicuser
1 Solution
 
mirzasCommented:
TAKEN is date time?

It is definitely possible - just provide more details about the table fields.
0
 
mimicuserAuthor Commented:
Sorry always forget how important the field types are:

POINT_DETAIL_ID = uniqueidentifier
VALUE = float
TAKEN = datetime
0
 
SreejithGCommented:
SELECT TOP 2 * FROM READING  ORDER BY TAKEN DESC

Assuming the TAKEN column stores the time stamp
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
mimicuserAuthor Commented:
Sorry Sreejith it only returns two rows. I need the result to look like:

POINT_DETAILID then the Latest Value then Second Latest Value for every point_detail in the table.

It will allow our consultants to look at pointdetail and compare the latest value against what it was before.
0
 
quincydudeCommented:
or is it
SELECT  TOP 2  *  FROM READING group by POINT_DETAIL_ID ORDER BY TAKEN DESC  
?
0
 
quincydudeCommented:
try
select POINT_DETAIL_ID, reading.TAKEN,VALUE
from reading 
where TAKEN in (select top 2 TAKEN from reading  re2 
where re2.POINT_DETAIL_ID=reading.POINT_DETAIL_ID ) 
order by POINT_DETAIL_ID

Open in new window

0
 
mimicuserAuthor Commented:
I am explaining my problem very badly. I have created a very simple word document wich contains an example table and the desired result, I hope this helps.

Again thanks for your efforts so far.
Example-Table-And-Result.doc
0
 
SharathData EngineerCommented:
In which database you are working? If it is SQL Server 2005 or 2008, this will work.

WITH CTETab(POINT_DETAIL_ID,TAKEN,VALUE,RowNum) AS
(SELECT  POINT_DETAIL_ID,TAKEN,VALUE,
                   ROW_NUMBER() OVER ( PARTITION BY POINT_DETAIL_ID ORDER BY TAKEN  DESC)  As RowNum
  FROM Example_Table)
 
SELECT A. POINT_DETAIL_ID, A.VALUE AS LATEST_VALUE, B.VALUE AS VALUE_BEFORE
FROM CTETab A
JOIN CTETab B
ON A. POINT_DETAIL_ID = B. POINT_DETAIL ID
WHERE A.RowNum = 1 AND B.RowNum = 2
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now