Limit rows returned - Page 2 - Microsoft SQL Server

7 New Member

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

Expand|Select|Wrap|Line Numbers

  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.

Apr 9 '08

Subscribe Reply

2479

Delerna

1,134

Recognized Expert Top Contributor

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.

Apr 23 '08 #11

adrianga

New Member

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?

Apr 29 '08 #12

Delerna

1,134

Recognized Expert Top Contributor

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.

Apr 29 '08 #13

Delerna

1,134

Recognized Expert Top Contributor

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.

Apr 30 '08 #14

adrianga

New Member

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

Apr 30 '08 #15

Similar topics

2420

database abstraction layer with limit / total rowcount support

by: Rainer Collet | last post by:

hi! i tested several php database abstraction layers (db, mdb(2), creole, adodb, etc), but i always missed one really important feature: i need a method for a limited select which gives me the resultset and the total number of rows the select would have returned without the limit. nativly this is very easy SELECT SQL_CALC_FOUND ROWS * FROM table LIMIT 0,10 in mysql for example.

PHP

1982

Python COM - limit on size/complexity of returned object?

by: Paul Keating | last post by:

I have a very simple COM server written in Python that is trying to return a two-dimensional array 268 x 20. The values it contains are some small integers, some short (<29 character) Unicode strings, and None. To isolate the problem I have taken out the code that builds the matrix from real data, and just substituted a literal tuple of tuples, like this: class AtlasSecurity:

Python

3059

Question about the LIMIT clause

by: Steve | last post by:

I've run in to a problem with a query I'm trying to write. I have attached a sample SQL script at the end of this post to show an overview of what I'm working with. I want to be able to use LIMIT to control how many rows from one table are returned, independent of how many rows there are in a second table that is joined to the first. When I execute the following SQL script:

MySQL Database

18149

LIMIT in MS SQL ??!!

by: Urban | last post by:

hi, I have a question. Maybe You know the equivalent to command LIMIT from MySQL I couldn`t find something like this in MS SQL PS I try to display 10 records begining form e.g. 4 sort by id something like: "SELECT * FROM table WHERE name=... LIMIT 4, 10 ORDER BY id" in MySQL

Microsoft SQL Server

10433

SQL Query to return rows only when a max limit on SUM has been reached.

by: rod | last post by:

Hello SQL gurus! I am trying to write a query that will return a set of continguous rows from a table, and limit the number of rows returned when a maximum total has been reached by adding a value in one of the columns. For example, the two columns below represent 2 columns in a table. a 2 b 2

Microsoft SQL Server

5819

Find original number of rows before applied LIMIT/OFFSET?

by: D. Dante Lorenso | last post by:

I need to know that original number of rows that WOULD have been returned by a SELECT statement if the LIMIT / OFFSET where not present in the statement. Is there a way to get this data from PG ? SELECT ... ; ----> returns 100,000 rows

PostgreSQL Database

2877

optimization with limit and order by in a view

by: elein | last post by:

Yes, I vacuumed. Reproduced on both 7.3.2 and 7.5. Brain dead java beans want order by clauses in views that they use. my view is: select .... from bigtable b left join lookuptable l order by bigkey desc;

PostgreSQL Database

6340

SQLite - total rows with limit and offset

by: C. | last post by:

Hi, First off, apologies for posting here - I can't find a dedicated SQLite newsgroup The other thing I can't find is how to retrieve the total number of rows which would have been returned by a query if it did not have a LIMIT and/or OFFSET clause. (kind of like SQL_CALC_FOUND_ROWS/FOUND_ROWS() in MySQL)

PHP

4340

Limit on SQLDataReader w/ Blobs

by: Garth Wells | last post by:

I use a filtered SELECT to populate the SQLDataReader (rdr) with a filename and a blob (pdf). I then use File.WriteAllBytes to write each pdf to disk. ---------------------------------------- rdr = command.ExecuteReader(); while (rdr.Read()) { byte BinaryImage = (byte)rdr;

C# / C Sharp

2549

indexing, limit problem

by: GeniusTse | last post by:

Hi all, I found a big performance difference between two query with different "Limit" and I think it is not make sense Table: person_item PKey: (person_id, time) Index: (time) Query1) SELECT item FROM person_item WHERE person_id = '3000' ORDER BY time DESC OFFSET 0 LIMIT 10; total runtime: 0.224 ms

PostgreSQL Database

10550

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

12042

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

11145

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

8706

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

6665

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

6846

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

5427

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

4950

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

3980

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General