February 24, 2011 at 1:37 am
Hi All,
How do i get the top and the bottom record for example below data
CompanyName ReminderNo
XYZ 1
XYZ 2
ABC 0
now i want the output like
CompanyName ReminderNo
XYZ 2
ABC 0
how do i get this.
any help is highly appreciated
thanks
fairozkhan
February 24, 2011 at 2:02 am
Just looking at your example data, it looks as though a very simple query will get what you want, but I suspect the requirements aren't quite as simple as you've explained.
SELECT CompanyName, max(ReminderNo)
FROM <tablename>
GROUP BY CompanyName
February 24, 2011 at 2:07 am
1)use order by ReminderNo asc/desc to see the list accordingly.
2) use top n in your select query to get top n number of records and use order by asc/desc to get list accordingly.
----------
Ashish
February 28, 2011 at 10:47 am
Try this:
USE <databasename>
GO
SELECT * into #maxRemind
FROM
(
SELECT TOP 1 CompanyName, ReminderNo
FROM <tablename>
ORDER BY ReminderNo DESC
) AS TopRemind
GO
SELECT * into #minRemind
FROM
(
SELECT TOP 1 CompanyName, ReminderNo
FROM <tablename>
ORDER BY ReminderNo ASC
) AS BotRemind
GO
SELECT * from #maxRemind
UNION ALL
SELECT * from #minRemind
GO
DROP TABLE #maxRemind
DROP TABLE #minRemind
GO
This inserts the top & bottom records into 2 temporary tables & then combines the results with two SELECTS and a UNION ALL.
-Bob
February 28, 2011 at 11:07 am
bobbalsman (2/28/2011)
Try this:
USE <databasename>
GO
SELECT * into #maxRemind
FROM
(
SELECT TOP 1 CompanyName, ReminderNo
FROM <tablename>
ORDER BY ReminderNo DESC
) AS TopRemind
GO
SELECT * into #minRemind
FROM
(
SELECT TOP 1 CompanyName, ReminderNo
FROM <tablename>
ORDER BY ReminderNo ASC
) AS BotRemind
GO
SELECT * from #maxRemind
UNION ALL
SELECT * from #minRemind
GO
DROP TABLE #maxRemind
DROP TABLE #minRemind
GO
This inserts the top & bottom records into 2 temporary tables & then combines the results with two SELECTS and a UNION ALL.
-Bob
Or you could just do this as a simple select with a union
SELECT TOP 1 CompanyName, ReminderNo, 'LastRecord'
FROM tablename
ORDER BY ReminderNo DESC
union all
SELECT TOP 1 CompanyName, ReminderNo, 'First Record'
FROM tablename
ORDER BY ReminderNo ASC
Or course this can potentially return the same record twice if there is only 1 record in the table. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 28, 2011 at 11:52 am
Actually, if you use a UNION only one ORDER BY clause is allowed & it's scope is the entire UNION'd query.
-Bob
February 28, 2011 at 12:09 pm
bobbalsman (2/28/2011)
Actually, if you use a UNION only one ORDER BY clause is allowed & it's scope is the entire UNION'd query.-Bob
:blush: It is Monday....you are correct.
something like this should work though.
SELECT CompanyName, ReminderNo tn
FROM tablename
join
(
select MAX(ReminderNO) as Last, MIN(ReminderNO) as First
from tablename
) agg on agg.Last = tn.ReminderNO or agg.First = tn.ReminderNO
ORDER BY tn.ReminderNo DESC
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 28, 2011 at 12:21 pm
One more little adjustment and that solution also works -
...move the 'tn' after '<tablename>'
So there we are - two answers for the price of one! 😀
-Bob
March 4, 2011 at 2:42 am
Its bit easy you may try this 🙂
SET NOCOUNT ON;
CREATE TABLE #StudentInfo
(
[StudentId] [int] NOT NULL,
[StudentName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
INSERT INTO #StudentInfo ([StudentId],[StudentName])
SELECT 1,'Student1' UNION ALL
SELECT 2,'Student2' UNION ALL
SELECT 3,'Student3' UNION ALL
SELECT 4,'Student4' UNION ALL
SELECT 5,'Student5' UNION ALL
SELECT 6,'Student6'
-- All records
SELECT *
FROM #StudentInfo NOLOCK
-- Top 2 records
SELECT TOP 2 *
FROM #StudentInfo NOLOCK
ORDER BY [StudentId] ASC
-- Botton 2 records
SELECT *
FROM (
SELECT TOP 2 *
FROM #StudentInfo NOLOCK
ORDER BY [StudentId] DESC
) InSql
ORDER BY [InSql].[StudentId] ASC
DROP TABLE #StudentInfo
March 4, 2011 at 3:47 am
Yogesh Potdar (3/4/2011)
Its bit easy you may try this...
There's no point specifying NOLOCK on a local temporary table, and the syntax that omits the WITH keyword and parentheses around the hint is deprecated anyway. Speaking of parentheses, we are also encouraged to use them around the TOP parameter - again 'TOP 2' is deprecated and only supported for backward compatibility; ...TOP (2) should be used for new code. Also, try:
SELECT StudentId,
Name
FROM (
SELECT TOP (1)
SI.StudentId,
SI. Name
FROM #StudentInfo AS SI
ORDER BY
SI.StudentId DESC
) AS TopRow
UNION ALL
SELECT BottomRow.StudentId,
BottomRow.Name
FROM (
SELECT TOP (1)
SI.StudentId,
SI. Name
FROM #StudentInfo AS SI
ORDER BY
SI.StudentId ASC
) AS BottomRow
ORDER BY
StudentId ASC
;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 4, 2011 at 4:44 am
Thanx - Regarding the parenthesis and standards, can you provide me the standards for writing the SQL Query, as i was looking for those 🙂
March 4, 2011 at 5:01 am
Yogesh Potdar (3/4/2011)
Thanx - Regarding the parenthesis and standards, can you provide me the standards for writing the SQL Query, as i was looking for those 🙂
Yes, the syntax is documented in the SQL Server help system known as 'Books Online'.
For example, the following entry describes the syntax and usage of Table Hints:
http://msdn.microsoft.com/en-us/library/ms187373.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply