There is a payments table with the following structure:

 user_id - идентификатор пользователя order_id - идентификатор платежа pay_dt - дата-время платежа

How to create a request that will return the third, for example, line by account, in the sort by pay_dt for each user?

The first can be obtained using TOP (1) + ORDER BY. And the third? DBMS My SQL

  • Specify the DBMS. And describe the problem in more detail. - pegoopik
  • one
    @pegoopik tried to save the question, can publish your answer - Bald
  • 2
    @Bald, "How to get a string with a specific number in a sorted set?" - sent acc. edit - pegoopik
  • one
    @ Alexander, get used to it. Unfortunately, there are now a lot of people who, upon seeing a question whose solutions do not know, begin to blame the question (in general, blame the mirror ...) - avp
  • one
    If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer). - Nicolas Chabanovsky ♦

2 answers 2

SQL Server; Oracle; PostgreSQL:

 SELECT user_id, pay_dt, order_id FROM( SELECT ROW_NUMBER()OVER(PARTITION BY user_id ORDER BY pay_dt, order_id)N, P.* FROM payments P )T WHERE N=3

UPD: deleted erroneous responses. did not consider what is needed for each user ...

UPD2: without samojoynov for MySQL only such solution comes to mind:

 SELECT user_id, pay_dt, order_id FROM( SELECT payments.*, @I := IF(user_id = @UserId, @I:=@I+1, @I:=1) N, @UserId := user_id user_id FROM payments, (SELECT @I := 0, @UserId := null)I ORDER BY user_id, pay_dt, order_id )T WHERE N=3

How it works: we sort in the order of user_id , pay_dt , see if the current user_id is equal to the previous one, then add one to @I , otherwise assign it 1 (start over numbering for the new user). Analogue of PARTITION BY in ROW_NUMBER other DBMS with numbering.

According to mike's advice:

 SELECT user_id, SUBSTR(GROUP_CONCAT(pay_dt ORDER BY pay_dt), 41, 19)pay_dt_3rd FROM payments GROUP BY user_id HAVING COUNT(*)>=3
  • in mysql mine there is no top , there it seems like limit - Bald
  • @Bald, exactly corrected, thanks. - pegoopik
  • @pegoopik By the way, at the initial statement of the question "get the date" (that is, one field), you could easily decide through substr(group_concat(date order by ...)) . And by the way, IF(user_id=@UserId,@I+1,1) more convenient than this huge case - Mike
  • @Mike, If I add, since we are talking exclusively about MySQL. and it is desirable to write in ANSII. Regarding the substr - the question was initially zaminusovali -> for my part there were attempts to pull it out, formulated in a more general form. Not very well. Write your answer with a comment, it will not be superfluous, the author certainly. - pegoopik
  • @pegoopik case of course, ansi, only here in oracle there wasn’t it until version 9, so compatibility in SQL is such a strained concept :) - Mike
 SELECT Good_Ids.user_id as user_id , (SELECT pay_dt FROM payments WHERE payments.user_id = Good_Ids.user_id ORDER BY pay_dt LIMIT 1 OFFSET 2) AS Third_Order FROM (SELECT user_id FROM payments GROUP BY user_id HAVING COUNT(*) >= 3) AS Good_Ids;

A little understood in the subject, I think this code will also work. It also included checking that there is a third payment for this user. What do you think?

  • you have two readings from payments. I added an example from Mike in response, look. - pegoopik