CODEWITHSUNDEEP
sqlpostgresql
DerrickHo328
DerrickHo328

Reputation: 4886

postgreSQL get index of a row that is outside the limit you searched for

I am new to SQL and I am not sure how to properly search my question so I will ask here.

Please see this link to see the SQL tables and queries I am working with

In this example there are 6 rows and I am limiting my search to start at the first index and give me at most 2. However, I would like to know the index of the row that has id 1.

When I use the query I describe in sqlfiddle, It shows me rows with id 5 and 23. But it doesn't include the row with id 1. However, I need to know the index of the row with id 1..

click here to see the full list

The link above prints out all of the rows and we can see that index 3 has the row containing id 1.

However I need to know that index without asking for the entire Array.

Why is this important? Well, lets say that we have 1 million rows. And if I ask for a million rows, that would mean allocating an array of one million. I could parse the array until I find the id I am looking for. However, allocating a million is way too costly.

Lets say for example that the row I am looking for resides in index 26, But I make my query so that it starts at index 0 and limits to 10. The array that I get from this query would not contain index 26. However I still need to know that it IS at index 26.

So this magic query would give me two things:

  1. the top ten rows of the sorted rows
  2. the index of a specified id (e.g. id of 1) regardless of its placement in the list.

Is this a possible query?

Clarification: I use the word index to mean the row number. If a we query a list of names from the db, we could get something like this:

bob
frank
dawn

then bob would be at index 0, frank would be at index 1 and dawn at index 2.

If I ORDER BY name ASC then the list of names would become

bob
dawn
frank

bob would be index 0 dawn would be index 1 and frank would be index 2.

I hope this makes things more clear.

Upvotes: 0

Views: 116

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270663

If you want the row number, use the row_number() function:

SELECT *
FROM (SELECT ud.id, ud.team_name, ui.name, ui.date_created,
             row_number() over (order by ui.name, ui.id) as rownumber
      FROM user_data ud JOIN
           user_infos ui
           ON ui.id = ud.id  
      WHERE ui.date_created BETWEEN NOW() - INTERVAL '1 year' AND NOW()  
     ) t
WHERE rownumber <= 10 or id = 1;

If you want them in order, just add order by rownumber as the last statement.

Upvotes: 1

Related Questions