I Want to get second entry date-time wise

Question

I want to get second entry in SQL

Consgno     Name      Entrydatetime

 111          A     01/03/2017 10:10:15
 111          A     01/03/2017 10:20:15
 111          A     01/03/2017 11:10:20
 222          B     02/03/2017 10:10:25
 222          B     02/03/2017 11:10:36
 333          C     06/03/2017 10:10:25
 333          C     07/03/2017 10:10:12
 444          D     04/03/2017 10:10:41
 444          D     04/03/2017 01:10:20
 444          D     06/03/2017 10:10:32

One Consgno has entered more than one time. And I want to output like this:

   Consgno  Name     Entrydatetime
    111      A      01/03/2017 10:20:15
    222      B      02/03/2017 11:10:36
    333      C      07/03/2017 10:10:12
    444      D      04/03/2017 01:10:20

http://stackoverflow.com/questions/tagged/greatest-n-per-group+sql — , Apr 20 '17 at 08:11
Tag the dbms you're using. When it comes to date/time many products are far from ANSI SQL compliant. — jarlh, Apr 20 '17 at 08:11
Possible duplicate of [SQL Server: SELECT only the rows with MAX(DATE)](http://stackoverflow.com/questions/7118170/sql-server-select-only-the-rows-with-maxdate) — Koby Douek, Apr 20 '17 at 08:11

score 1 · Answer 1 · answered Apr 20 '17 at 08:16

1

sql server/oracle/Postgres:

with CTE as
(
select MyTable.*, row_number() over(partition by consgno order by entrydatetime) as rn
from MyTable
)
select *
from CTE
where rn = 2

answered Apr 20 '17 at 08:16

JohnHC

10,935
1
24
40

Oto Shavadze · Answer 2 · 2017-04-20T09:00:29.017

(Works only, if you DB supports window analytic functions)

In case, if there is chance that Entrydatetime may repeated for same Consgno, and you want second date-time wise, then you can use:

select * from (
    select   MyTable.*, 
    dense_rank() over(partition by consgno order by entrydatetime) as rnk,
    row_number() over(partition by consgno,entrydatetime order by entrydatetime) as rn
    from MyTable
) t
where  rnk = 2 and rn = 1

(Note, that if consgno have same Entrydatetime for every row, this query not returns that consgno at all.)

score -1 · Answer 3 · answered Apr 20 '17 at 08:18

-1

Use OFFSET command in your query eg.

SELECT DISTINCT  Consgno,Name,Entrydatetime  FROM tbl_test ORDER BY
    Consgno
LIMIT 1 OFFSET 1;

This example selects the 2nd row. The Consgno has to be DISTINCT

answered Apr 20 '17 at 08:18

Abel Masila

736
2
7
25

Surely you need to add `Entrydatetime` into the `order by`? – JohnHC Apr 20 '17 at 08:19
Why `SELECT DISTINCT` when `LIMIT 1`? (Btw, are you sure OP is using a dbms that support that vendor specific `LIMIT`?) – jarlh Apr 20 '17 at 08:46
either way..it will work without `LIMIT` for DBMS which support `DISTINCT`. Just giving a general solution – Abel Masila Apr 20 '17 at 08:49

score -2 · Answer 4 · answered Apr 20 '17 at 08:27

-2

You can refer my query. Hope to help, my friend :))

With CTE
AS
(
select *, ROW_NUMBER() over (partition by Consgno order by Consgno) as Number
from Test)

select * from CTE where Number = 2

answered Apr 20 '17 at 08:27

Tomato32

2,145
1
10
10

Wrong `order by` based on expected output – JohnHC Apr 20 '17 at 08:29
@JohnHC: I have tested and it returned output as expected, my friend! – Tomato32 Apr 20 '17 at 08:37
This time, yes. Over a larger dataset this will not guarantee that it will return the second occurence (by date) that OP has implied with the expected output – JohnHC Apr 20 '17 at 08:45
@JohnHC. Yeah, you're right. But he said he want to get second entry in SQL, not depend on Entrydatetime field. – Tomato32 Apr 20 '17 at 08:48

I Want to get second entry date-time wise

4 Answers4