sql statement to select previous rows to a search param

Question

Im after an sql statement (if it exists) or how to set up a method using several sql statements to achieve the following.

I have a listbox and a search text box.

in the search box, user would enter a surname e.g. smith. i then want to query the database for the search with something like this :

select * FROM customer where surname LIKE searchparam

This would give me all the results for customers with surname containing : SMITH . Simple, right?

What i need to do is limit the results returned. This statement could give me 1000's of rows if the search param was just S. What i want is the result, limited to the first 20 matches AND the 10 rows prior to the 1st match.

For example, SMI search:

Sives
Skimmings
Skinner
Skipper
Slater
Sloan
Slow
Small
Smallwood
Smetain
Smith ----------- This is the first match of my query. But i want the previous 10 and following 20.
Smith
Smith
Smith
Smith
Smoday
Smyth
Snedden
Snell
Snow
Sohn
Solis
Solomon
Solway
Sommer
Sommers
Soper
Sorace
Spears
Spedding

Is there anyway to do this? As few sql statements as possible.

Reason? I am creating an app for users with slow internet connections.

I am using POSTGRESQL v9

Thanks Andrew

PinnyM · Accepted Answer · 2014-01-28T14:43:53.303

2

WITH ranked AS (
  SELECT *, ROW_NUMBER() over (ORDER BY surname) AS rowNumber FROM customer
)
SELECT ranked.*
FROM ranked, (SELECT MIN(rowNumber) target FROM ranked WHERE surname LIKE searchparam) found
WHERE ranked.rowNumber BETWEEN found.target - 10 AND found.target + 20
ORDER BY ranked.rowNumber

SQL Fiddle here. Note that the fiddle uses the example data, and I modified the range to 3 entries before and 6 entries past.

edited Jan 28 '14 at 14:43

answered Jan 28 '14 at 14:35

PinnyM

35,165
3
73
81

WOW, just WOW. I didn't think this was possible, but like i said i am no guru. Thanks sooooooo much. And i actually think i follow it too! – Andrew - Avro Computers Jan 29 '14 at 10:56
oh no! Just saw the search was Smith, so i changed the search to be Smi and it failed to produce any findings! – Andrew - Avro Computers Jan 29 '14 at 11:05
oops... just remembered if i add % before and/or after searchparam it will give results for smi as the search param – Andrew - Avro Computers Jan 29 '14 at 12:00

score 0 · Answer 2 · answered Jan 28 '14 at 14:34

0

I'm assuming that you're looking for a general algorithm ...

It sounds like you're looking for a combination of finding the matches "greater than or equal to smith", and "less than smith".

For the former you'd order by surname and limit the result to 20, and for the latter you'd order by surname descending and limit to 10.

The two result sets can then be added together as arrays and reordered.

answered Jan 28 '14 at 14:34

David Aldridge

51,479
8
68
96

You could do this, although that would require a UNION (or 2 roundtrips) - somewhat slower than using a CTE for this purpose... – PinnyM Jan 28 '14 at 14:49
Thanks for this, I was planning on doing something like this if i could not get it done by a single sql statement. – Andrew - Avro Computers Jan 29 '14 at 10:57

score 0 · Answer 3 · edited May 23 '17 at 12:11

0

I think you need to use ROW_NUMBER() (see this link).

 WITH cust1 AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY surname) as numRow FROM customer
)
SELECT c1.surname, c1.numRow, x.flag
FROM cust1 c1, (SELECT *,   
                case when numRow = (SELECT MIN(numRow) FROM cust1 WHERE surname='Smith') then 1 else 0 end as flag
               FROM cust1) x
WHERE x.flag = 1 and c1.numRow BETWEEN x.numRow - 1 AND x.numRow + 1
ORDER BY c1.numRow

SQLFiddle here.

This works, but the flag finally isn't necessary and it would be a query like PinnyM posts.

edited May 23 '17 at 12:11

Community

1
1

answered Jan 28 '14 at 14:46

carexcer

1,407
2
15
27

This isn't going to work properly - it limits the result to `x.flag = 1` and only the rows that match the exact surname will be returned. – PinnyM Jan 28 '14 at 14:48
I didn't test it, but the searched row will be the only which has flag=1, and getting it then you have the row number of that row and you can use it to apply the BETWEEN condition. At least that is the idea and I think it will works, but correct me now if I'm wrong please. – carexcer Jan 28 '14 at 14:52
The desired result included surnames that did not match, but are just above or below the first actual match. Since you are filtering on exact matches, it can't possibly work - try it out and see. You can use SQLFiddle.com to demonstrate your findings if I'm wrong. – PinnyM Jan 28 '14 at 14:54
You were right, at least when you said that query doesn't works, but the subquery returns all the matches (but it was not the desired query). I've updated with a query that works, but finally the best way was your query, nice job. – carexcer Jan 28 '14 at 15:25
Thanks heaps, Its a bit harder to follow, but still great work. – Andrew - Avro Computers Jan 29 '14 at 11:02

score 0 · Answer 4 · edited May 23 '17 at 12:11

A variation on @PinnyM's solution:

WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() over (ORDER BY surname) AS rowNumber
  FROM customer
),
minrank AS (
  SELECT
    *,
    MIN(CASE WHEN surname LIKE searchparam THEN rowNumber END) OVER () AS target
  FROM ranked
)
SELECT
  surname
FROM minrank
WHERE rowNumber BETWEEN target - 10 AND target + 20
;

Instead of two separate calls to the ranked CTE, one to get the first match's row number and the other to read the results from, another CTE is introduced to serve both purposes. Can't speak for PostgreSQL but in SQL Server this might result in a better execution plan for the query, although in either case the real efficiency would still need to be verified by proper testing.

sql statement to select previous rows to a search param

4 Answers4