To find the first and last records in a given table

Question

1.20/5 (2 votes)

See more:

heyy...

I want a query, to find the first and last records in a given table but I want the result by some condition..

here is my table data :

CSS

No.       starttime  Endtime
-------   ---------  --------
0500000001  06:52   06:59
0500000001  07:00   07:27
0500000001  07:28   08:09
0500000001  08:14   08:40
0500000001  08:42   10:00
0500000001  10:01   11:29
0500000001  11:31   12:41
0500000001  12:43   13:28
0500000144  10:59   11:03
0500000144  11:03   11:04
0500000144  11:05   11:07
0500000144  11:08   11:37
0500000144  11:37   09:47
0500000144  09:47   09:51
0500000144  09:52   09:53
0500000144  09:54   10:46
0500000144  10:47   11:12
0500000144  11:13   11:16
0500000144  11:17   13:02
0500000144  13:02   09:29
0500000144  13:29   09:29

here I want My result as like this

No.          starttime     Endtime
  ------        ----------   --------
  0500000001    06:52         13:28
  0500000144    10:59         09:29

ie. first record and last record of No.

Any idea how to write query in mysql...?

Posted 13-Aug-13 21:01pm

aravindnass

Updated 11-Oct-22 1:23am

Add a Solution

8 solutions

Solution 5

Do you Google[^] it?
Try in this format..
To get the first record:

SQL

select col1,col2,col3 from tab1 order by col1 asc limit 1;

To get the last record:

SQL

select col1,col2,col3 from tab1 order by col1 desc limit 1;

Posted 13-Aug-13 21:44pm

ridoy

Updated 13-Aug-13 21:48pm

v4

Solution 1

Try like this

SQL

DECLARE @Vehicle TABLE (
VehicleNo NVARCHAR(50),Datetime1 Datetime,Datetime2 Datetime);
INSERT INTO @Vehicle (VehicleNo,Datetime1,Datetime2)
VALUES('0500000001','06:52','06:59')
INSERT INTO @Vehicle (VehicleNo,Datetime1,Datetime2)
VALUES('0500000001','07:00','07:27')
INSERT INTO @Vehicle (VehicleNo,Datetime1,Datetime2)
VALUES('0500000144','10:59','11:03')

SELECT top 1 * FROM @Vehicle WHERE VehicleNo=(SELECT MIN(VehicleNo) FROM @Vehicle)
UNION
SELECT top 1 * FROM @Vehicle WHERE VehicleNo=(SELECT MAX(VehicleNo) FROM @Vehicle)

Posted 13-Aug-13 21:07pm

Mukesh Ghosh

Comments

aravindnass 14-Aug-13 3:35am

I want mysql query..TOP keyword not working...

Solution 6

To get the first 10 records:
select col1,col2,col3 from tab1 order by col1 asc limit 10;

To get the last 10 records:
select col1,col2,col3 from tab1 order by col1 asc limit 10;

Posted 27-Nov-18 17:20pm

Member 10428928

Comments

CHill60 28-Nov-18 7:19am

No different from Solution 5 posted 5 years ago and the OP only wanted the first and last single record not first 10 and last 10.
If you are going to post solutions to questions that have already been answered, especially if one or more solutions have been accepted, then make sure you are bringing something new to the thread and not just repeating what someone else has already posted

Solution 7

Try this

content of the table

SELECT TOP [Number]
      ,[StratTime]
      ,[EndTime]
  FROM [TestingData].[dbo].[Table_1]

Number	StratTime	EndTime
500000001	06:52:00.0000	06:59:00.0000
500000001	08:00:00.0000	08:45:00.0000
500000001	07:00:00.0000	07:45:00.0000

Result

SELECT [Number]
      ,Min(StratTime)
      ,Max(EndTime)
  FROM [TestingData].[dbo].[Table_1]
  Group by Number

Number	(No column name)	(No column name)
500000001	06:52:00.0000	08:45:00.0000

Posted 29-Nov-18 1:02am

Vijay Bavoor

Comments

Richard Deeming 29-Nov-18 7:35am

As already mentioned in solution #4.

CHill60 29-Nov-18 7:56am

And the same problem as Solution 4 - you did not select the records. Read the question carefully!

Solution 8

SELECT * FROM(SELECT E.*,ROW_NUMBER() OVER(ORDER BY ROWNUM ASC) AS RS FROM EMP_INFO E) WHERE RS=1
UNION ALL
SELECT * FROM(SELECT E.*,ROW_NUMBER() OVER(ORDER BY ROWNUM DESC) AS RS FROM EMP_INFO E) WHERE RS=1;

Posted 19-Aug-20 22:39pm

Member 14918713

Comments

Richard MacCutchan 20-Aug-20 4:42am

Already well answered seven years ago.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Code-Hunt · Accepted Answer · 2013-08-13T21:08:00

Solution 2

http://stackoverflow.com/questions/2735395/how-to-find-first-and-last-record-from-mysql-table

get-the-first-and-last-record-of-db[^]

Check the link..hope it will help..

Posted 13-Aug-13 21:08pm

Code-Hunt

Updated 13-Aug-13 21:40pm

v2

syed shanu · Accepted Answer · 2013-08-13T21:17:00

Solution 3

To get First and Last record from SQL Query try like this.

SQL

SELECT *
FROM table1
WHERE id IN (
SELECT TOP 1 MIN(id) ids
FROM table1
UNION ALL
SELECT TOP 1 MAX(id) ids
FROM table1)

In your case

SQL

SELECT *
FROM yourTableName
WHERE id No(
SELECT TOP 1 MIN(No) ids
FROM yourTableName
UNION ALL
SELECT TOP 1 MAX(No) ids
FROM yourTableName
)

Posted 13-Aug-13 21:17pm

syed shanu

Comments

aravindnass 14-Aug-13 3:35am

I want mysql query..TOP keyword not working...

syed shanu 14-Aug-13 3:43am

In case of MYSQL You need to limit.
select * from table where id= (select id from tab1 order by col1 asc limit 1;)
or id=(select id from tab1 order by col1 desc limit 1; )

Check this links
http://stackoverflow.com/questions/2735395/how-to-find-first-and-last-record-from-mysql-table
http://stackoverflow.com/questions/1143487/how-to-get-the-first-and-last-record-of-db

http://stackoverflow.com/questions/14334805/mysql-getting-first-and-last-record-of-grouping

Mean time try to use google in google you can get all your answers.

ArunRajendra · Accepted Answer · 2013-08-13T21:28:00

Solution 4

Try this code. Replace the table and column name as per your design.

SQL

select No, MIN(starttime),MAX(endtime) from table1
group by No

Posted 13-Aug-13 21:28pm

ArunRajendra