Limit rows returned

[adrianga]

Hi all,
My English is not the best so it will be best if I show you an example:
I have a query:

 SELECT Make as [MAKE], Model as [MODEL], DERIVATIVE, [PRICE]
from MY_VIEW

and here is what it returns:
[HTML]
MAKE MODEL DERIVATIVE PRICE
==== ======= ========= =====
FORD MONDEO HATCHBACK SPEC EDS 2.0 Edge 5dr 8250.00
FORD MONDEO HATCHBACK SPEC EDS 2.0 Edge 5dr 8250.00
FORD RANGER DIESEL Pick Up Double Cab TD 4WD 6168.75
FORD RANGER DIESEL Pick Up XLT Super TD 4WD 7696.25
FORD RANGER DIESEL Pick Up Double Cab TD 4WD 6462.50
FORD RANGER DIESEL Pick Up Double Cab TD 4WD 7167.50
FORD RANGER DIESEL Pick Up Double Cab TD 4W 6374.38
FORD TRANSIT 260 SWB DIESEL FWD Low Roof Van TDi 85ps 2.2 6521.25
FORD TRANSIT 260 SWB DIESEL FWD Low Roof Van TDi 85ps 2.2 7784.38
FORD TRANSIT 260 SWB DIESEL FWD Low Roof Van TDi 85ps 2.2 7872.50
FORD TRANSIT 260 SWB DIESEL FWD Low Roof Van TDi 85ps 2.2 6961.88
FORD TRANSIT 260 SWB DIESEL FWD Low Roof Van TDi 85ps 2.2 6315.63
FORD TRANSIT 280 LWB DIESEL RWD Medium Roof Van TDi 90ps 6345.00
FORD TRANSIT 280 LWB DIESEL RWD Medium Roof Van TDi 90ps 9517.50
FORD TRANSIT 350 LWB DIESEL RWD Medium Roof Van TDCi 100ps 13512.50
FORD TRANSIT 350 LWB DIESEL RWD High Roof Van TDi 90ps 10398.75
FORD TRANSIT 350 LWB DIESEL RWD High Roof Van TDi 90ps 9165.00
FORD TRANSIT 350 LWB DIESEL RWD High Roof Van TDi 90ps 8577.50
FORD TRANSIT 350 LWB DIESEL RWD High Roof Van TDi 90ps 7138.13
FORD TRANSIT 350 LWB DIESEL RWD High Roof Van TDi 90ps 8783.13
FORD TRANSIT 350 LWB DIESEL RWD High Roof Van TDi 90ps 7020.63
[/HTML]
Can any of you guys let me know what would be the easiest way to limit the results returned to display only say top 3 cars of the same model. ie: only 3 of TRANSIT 350 LWB DIESEL RWD models?

Thanks in advance.

[ck9663]

Hi all,
My English is not the best so it will be best if I show you an example:
I have a query:

 SELECT Make as [MAKE], Model as [MODEL], DERIVATIVE, [PRICE]
from MY_VIEW

and here is what it returns:
[HTML]
MAKE MODEL DERIVATIVE PRICE
==== ======= ========= =====
FORD MONDEO HATCHBACK SPEC EDS 2.0 Edge 5dr 8250.00
FORD MONDEO HATCHBACK SPEC EDS 2.0 Edge 5dr 8250.00
FORD RANGER DIESEL Pick Up Double Cab TD 4WD 6168.75
FORD RANGER DIESEL Pick Up XLT Super TD 4WD 7696.25
FORD RANGER DIESEL Pick Up Double Cab TD 4WD 6462.50
FORD RANGER DIESEL Pick Up Double Cab TD 4WD 7167.50
FORD RANGER DIESEL Pick Up Double Cab TD 4W 6374.38
FORD TRANSIT 260 SWB DIESEL FWD Low Roof Van TDi 85ps 2.2 6521.25
FORD TRANSIT 260 SWB DIESEL FWD Low Roof Van TDi 85ps 2.2 7784.38
FORD TRANSIT 260 SWB DIESEL FWD Low Roof Van TDi 85ps 2.2 7872.50
FORD TRANSIT 260 SWB DIESEL FWD Low Roof Van TDi 85ps 2.2 6961.88
FORD TRANSIT 260 SWB DIESEL FWD Low Roof Van TDi 85ps 2.2 6315.63
FORD TRANSIT 280 LWB DIESEL RWD Medium Roof Van TDi 90ps 6345.00
FORD TRANSIT 280 LWB DIESEL RWD Medium Roof Van TDi 90ps 9517.50
FORD TRANSIT 350 LWB DIESEL RWD Medium Roof Van TDCi 100ps 13512.50
FORD TRANSIT 350 LWB DIESEL RWD High Roof Van TDi 90ps 10398.75
FORD TRANSIT 350 LWB DIESEL RWD High Roof Van TDi 90ps 9165.00
FORD TRANSIT 350 LWB DIESEL RWD High Roof Van TDi 90ps 8577.50
FORD TRANSIT 350 LWB DIESEL RWD High Roof Van TDi 90ps 7138.13
FORD TRANSIT 350 LWB DIESEL RWD High Roof Van TDi 90ps 8783.13
FORD TRANSIT 350 LWB DIESEL RWD High Roof Van TDi 90ps 7020.63
[/HTML]
Can any of you guys let me know what would be the easiest way to limit the results returned to display only say top 3 cars of the same model. ie: only 3 of TRANSIT 350 LWB DIESEL RWD models?

Thanks in advance.

use filter conditions

WHERE MODEL = 'TRANSIT 350 LWB DIESEL RWD' to filter out your result. Here's the full WHERE syntax. And here's more about search condition.

To reduce the number of rows returned, use the TOP clause. Here's more about the TOP clause.

-- CK

[Delerna]

Try this. So long as your table dosn't hold huge amounts of data it should run reasonably well for you

SELECT Make,Model,Derivative,Price
FROM
(   SELECT a.Make,a.Model,a.Derivative,a.Price,
             (   SELECT Count(Price) 
                 FROM YourTable b 
                 WHERE a.Make=b.make 
                   AND a.model=b.model 
                   AND a.derivative=b.derivative 
                   AND a.price>b.price
             )+1 as Pos
    FROM YourTable a
)a
where pos<4
 

[ck9663]

I missed that part. My bad.

-- CK

[adrianga]

Thanks guys,
I do have a basic idea on how to use filter conditions and 'top' clause but I couldn't figure out how to filter the results in the way described by me earlier.
@Delerna doesn't seem to be doing exactly what I would want - it still displays more than 3 same models of cars. It does seem to be limiting the results returned though. I don't understand why are you counting the price in one of the subselects? Would you be able to explain that to me in some simple way ? Thanks!

[Delerna]

The intent is to return a number that identifies the rank of a price within a set of prices for a given make model and derivative.

What it does is count the number of prices that are less than the price that a record has. So
for the higest price the count will be 0 prices that are higher
for the second highest price the count will be 1 price that is higher
for the third higest price the count will be 2 prices that are higher
....etc

try running a query from the subquery part and you will see what it does

  SELECT a.Make,a.Model,a.Derivative,a.Price,
             (   SELECT Count(Price) 
                 FROM YourTable b 
                 WHERE a.Make=b.make 
                   AND a.model=b.model 
                   AND a.derivative=b.derivative 
                   AND a.price>b.price
             )+1 AS Pos
    FROM YourTable a
 

[adrianga]

The intent is to return a number that identifies the rank of a price within a set of prices for a given make model and derivative.

What it does is count the number of prices that are less than the price that a record has. So
for the higest price the count will be 0 prices that are higher
for the second highest price the count will be 1 price that is higher
for the third higest price the count will be 2 prices that are higher
....etc

try running a query from the subquery part and you will see what it does

  SELECT a.Make,a.Model,a.Derivative,a.Price,
             (   SELECT Count(Price) 
                 FROM YourTable b 
                 WHERE a.Make=b.make 
                   AND a.model=b.model 
                   AND a.derivative=b.derivative 
                   AND a.price>b.price
             )+1 AS Pos
    FROM YourTable a
 

Thanks!
This works great..but :)... what do I do when there is few of the same models with the same price? They all get the same position unfortunately :(
Also it will only allow me to limit the results by top x of derivative, I would like to limit it to top x of model - so if there are 5 ford transit LWB and 5 ford transit RWB I would only like to display top 3 of ford (make) transit (model).
I hope you know what I am saying @Delerna?
I am sure we are heading in the right direction though - as this is exactly what I was thinking, just a 'simple' query, no cursors.
But if it can't be done without cursors than maybe you have an advice to me how to do this wiuth cursors than?

[Delerna]

The model field looks a little ugly to do the top 3 make/model.
There is one hope that I can think of that it can be achieved

In these the model you want to group by is TRANSIT
TRANSIT 350 LWB DIESEL RWD
TRANSIT 260 SWB DIESEL FWD

and in this the model you want to group by is RANGER
RANGER DIESEL

and in this the model you want to group by is MONDEO
MONDEO HATCHBACK SPEC EDS

There is a patern in the above examples that can be used to extract the model for use with the price ordering.

The pattern is, everything to the left of the first space is the model
If that pattern remains true for all records then I can show you how to do it.

As for the situation where there are two or more prices that are the same....hmmm
I'll have to think about that one, not sure if there is a way around it or not.

[adrianga]

Thanks again for trying. I have a few friends of mine thinking about this as we all believe this is possible to do with a Select statement. But I think here I didn't explain clearly what I want to achieve:
Having table that looks like this:

MAKE      MODEL                        DERIVATIVE                PRICE
====      =======                       =========                 =====
FORD    MONDEO HATCHBACK SPEC EDS   2.0 Edge 5dr                 8250.00
FORD    MONDEO HATCHBACK SPEC EDS   2.0 Edge 5dr                 8250.00
FORD    RANGER DIESEL               Pick Up Double Cab TD 4WD  6168.75
FORD    RANGER DIESEL               Pick Up XLT Super TD 4WD  7696.25
FORD    RANGER DIESEL               Pick Up Double Cab TD 4WD  6462.50
FORD    RANGER DIESEL               Pick Up Double Cab TD 4WD  7167.50
FORD    RANGER DIESEL               Pick Up Double Cab TD 4W  6374.38
FORD    TRANSIT 260 SWB DIESEL FWD  Low Roof Van TDi 85ps 2.2 6521.25
FORD    TRANSIT 260 SWB DIESEL FWD  Low Roof Van TDi 85ps 2.2 7784.38
FORD    TRANSIT 260 SWB DIESEL FWD  Low Roof Van TDi 85ps 2.2 7872.50
FORD    TRANSIT 260 SWB DIESEL FWD  Low Roof Van TDi 85ps 2.2 6961.88
FORD    TRANSIT 260 SWB DIESEL FWD  Low Roof Van TDi 85ps 2.2 6315.63
FORD    TRANSIT 280 LWB DIESEL RWD  Medium Roof Van TDi 90ps  6345.00
FORD    TRANSIT 280 LWB DIESEL RWD  Medium Roof Van TDi 90ps  9517.50
FORD    TRANSIT 350 LWB DIESEL RWD  Medium Roof Van TDCi 100ps    13512.50
FORD    TRANSIT 350 LWB DIESEL RWD  High Roof Van TDi 90ps    10398.75
FORD    TRANSIT 350 LWB DIESEL RWD  High Roof Van TDi 90ps    9165.00
FORD    TRANSIT 350 LWB DIESEL RWD  High Roof Van TDi 90ps    8577.50
FORD    TRANSIT 350 LWB DIESEL RWD  High Roof Van TDi 90ps    7138.13
FORD    TRANSIT 350 LWB DIESEL RWD  High Roof Van TDi 90ps    8783.13
FORD    TRANSIT 350 LWB DIESEL RWD  High Roof Van TDi 90ps    7020.63
 

what I want to achieve is results like this:

MAKE      MODEL                        DERIVATIVE                PRICE
====      =======                       =========                 =====
FORD    MONDEO HATCHBACK SPEC EDS   2.0 Edge 5dr                 8250.00
FORD    MONDEO HATCHBACK SPEC EDS   2.0 Edge 5dr                 8250.00
FORD    RANGER DIESEL               Pick Up Double Cab TD 4WD  6168.75
FORD    RANGER DIESEL               Pick Up XLT Super TD 4WD  7696.25
FORD    RANGER DIESEL               Pick Up Double Cab TD 4WD  6462.50
FORD    TRANSIT 260 SWB DIESEL FWD  Low Roof Van TDi 85ps 2.2 6521.25
FORD    TRANSIT 260 SWB DIESEL FWD  Low Roof Van TDi 85ps 2.2 7784.38
FORD    TRANSIT 260 SWB DIESEL FWD  Low Roof Van TDi 85ps 2.2 7872.50
FORD    TRANSIT 280 LWB DIESEL RWD  Medium Roof Van TDi 90ps  6345.00
FORD    TRANSIT 280 LWB DIESEL RWD  Medium Roof Van TDi 90ps  9517.50
FORD    TRANSIT 350 LWB DIESEL RWD  Medium Roof Van TDCi 100ps    13512.50
FORD    TRANSIT 350 LWB DIESEL RWD  High Roof Van TDi 90ps    10398.75
FORD    TRANSIT 350 LWB DIESEL RWD  High Roof Van TDi 90ps    9165.00
 

So this is like a filtering by model on results returned. It doesn't mean I only want 3 Ford Transits, it can be 3 ford transit 350 LWB Diesel as this is the full model name. The only flaw with your query was when few models have the same price they will get the same position (POS) and they might be displayed if there are no cars with lower price if you know what I am saying. Example:

MAKE    MODEL    DERIVATIVE    colour    PRICE    POS
NISSAN    NOTE HATCHBACK    1.4 Acenta 5dr    CAYMAN BLUE    8025.00    1
NISSAN    NOTE HATCHBACK    1.4 Acenta 5dr    BLACK    8025.00           1
NISSAN    NOTE HATCHBACK    1.4 Acenta 5dr    INTENSE BLUE    8025.00     1
NISSAN    NOTE HATCHBACK    1.4 Acenta 5dr    BLACK    8025.00         1
NISSAN    NOTE HATCHBACK    1.4 Acenta 5dr    FADED DENIM    8025.00    1
NISSAN    NOTE HATCHBACK    1.4 Acenta 5dr    BLACK    8025.00      1
NISSAN    NOTE HATCHBACK    1.4 Acenta 5dr    BLACK    8025.00      1
NISSAN    NOTE HATCHBACK    1.4 Acenta 5dr    BLADE SILVER    8025.00    1
NISSAN    NOTE HATCHBACK    1.4 Acenta 5dr    INTENSE BLUE    8025.00    1
NISSAN    NOTE HATCHBACK    1.4 Acenta 5dr    BLACK    8025.00          1
NISSAN    NOTE HATCHBACK    1.4 Acenta 5dr    BLADE SILVER    8025.00    1
 

So my question is - do you have a suggestion on how can I deal with same priced models? Thanks

[adrianga]

Sorry for follow up but I finally got it!

SELECT * FROM(SELECT REGNO,Make as [MAKE], Model as [MODEL], DERIVATIVE,colour, PRICE,
        ( SELECT COUNT(PRICE) AS POS
                from MY_VIEW b
                WHERE a.make=b.make and a.model = b.model and a.price>b.price
        )+1  AS POS,( SELECT COUNT(PRICE) AS POS
                from MY_VIEW b
                WHERE a.make=b.make and a.model = b.model and a.price=b.price and a.regno>b.regno
        )+1  AS POS2
from MY_VIEW a) z 
WHERE POS<4 AND POS2<4
 

I know I should have mention that in my view I also have unique reg number for each car maybe you would come up with solution for me earlier.
But I finally got your logic and used it properly: so what I am doing now is first checking position in results based on price but if there is more than one car with the same price,the same make and same model than I am checking for position based on reg number.
Also in your suggested query you have been counting how many same make model and derivative cars have price higher than ones already returned in the results and that's why I had more than 3 same models in results even if the price was different - I only had to remove 'a.derivative=b.derivative' and it worked.
Well, huge thanks @delerna as I don't think there is many people out there with solution like this one. Few of my colleagues - software developers were helping me to come up with a solution and they couldn't 'hack it' :)

[Delerna]

You are very welcome and I am glad you now have a solution.
Oh and yes, knowing about REGNO could have made a difference.

This technique is very good at doing the sort of thing you were requiring.
I do not recommend doing it with tables that have lots of records however.
This is because the subquery gets executed once for each and every record.
so if you had 10,000,000 records the subquery gets executed 10,000,000 times.
The query is working with something like 100,000,000,000,000 records. As you can imagine, that could become very slow.

[adrianga]

You are very welcome and I am glad you now have a solution.
Oh and yes, knowing about REGNO could have made a difference.

This technique is very good at doing the sort of thing you were requiring.
I do not recommend doing it with tables that have lots of records however.
This is because the subquery gets executed once for each and every record.
so if you had 10,000,000 records the subquery gets executed 10,000,000 times.
The query is working with something like 100,000,000,000,000 records. As you can imagine, that could become very slow.

Yes, I have actually notice that execution speed is not the best. I don't have 10.000.000 records but it already takes a while to load results. Is there anything else that you could recommend to achieve what I want?

[Delerna]

There are 1001 ways to skin a cat.
For example
You could add an extra field to hold the prices relative position to the table and then run an update query on that field before you actually run the query.

That way the subquery that detemines a rows position in the price range would be removed from the selection query and used as the update query.
It would then only be run once insted of once for every row.

If you didn't want to add rows to the table then you could use a table variable with the extra field in that. Then you could do the update and select I mentioned above.
Warning, table variables are like a table and its data that exists totally in system memory so............

there would be other ways.

[Delerna]

There is a thread in the Access forum Here with a very similar requirement.
This one wants the rows with the shortest 2 distances between points in 12 sided polygons. The technique is reported to be slow there also. I must say, most of my development is with SQLServer and I have used this technique myself on resonably large tables without too much of a speed issue. I do have indexes though.

[adrianga]

I will play around with indexes and maybe try adding new field to my table. Thanks a lot for all your help!