Limiting records joined with a JOIN
-
Is there a simple (and fast) way to limit the records joined?
Table A has one record in it. Table B has 5 records. All of Table B's records match Table A's. If I were to join the two together I would get 5 records back (one for each record in table B.). I only want the FIRST record from Table B that matches.
Is there a way to do something like a TOP 1 on the join?
-
Would not someting like
SELECT Distinct TableA.one, TableB.one
FROM TableA, TableB
-
I have tried that but there are quite a few columns and a decent amount of records, so the distinct is very costly. It does work, but I was hoping for a way around it that was a might bit faster.
-
Can we assume that all the "matching" records in table b are not exaclty the same? So what is different about these matching records?
-
There all sorts of different data in them...some text fields, some dates, etc. The rows in table B are never exactly the same. Im not concerned as to which piece of data comes back, so long as only one row does.
-
I don't know how efficient this will be, and it may drag down your performance if the resultset is too large, but how about using the Top clause in a correlated subquery? For example, using pubs:
SELECT a.*, b.* FROM publishers a inner join titles b on b.pub_id = a.pub_id WHERE b.title_id in (SELECT top 1 b2.title_id from titles b2 where a.pub_id = b2.pub_id)
Mark -
Try using the SET ROWCOUNT command...
Example:
/* Limit the result set to just 1 row*/
SET ROWCOUNT 1
/*Here comes your query... */
/* Disable the result set limit, so it doesn't apply for other queries*/
SET ROWCOUNT 0
That would work if you need just the first matching row in your given order...
Let me know if that helped you!
Best regards,Nicolas Donadio
SW Developer
-
One way that I've done this was by using a derived table which does a group by and selects the MIN of the identity (if it exists). That identifies the first row. Then use a correlated query to get the data for that record. For example:
SELECT * FROM tblA AS A (NOLOCK)
INNER JOIN
(
SELECT tblAsForeignKey, MIN(tblAsIdentity) as "tblAsIdentity"
FROM tblA (NOLOCK)
GROUP BY tblAsForeignKey
) SUB ON A.tblAsIdentity = SUB.tblAsIdentity
But I'm not sure if this is the fastest option, just something I've done in the past.
-
Why not just do it plain and simple?
SELECT TOP 1 b.column1, b.column2, ....
FROM tableA a
JOIN tableB b
ON a.PK = b.PK
/Kenneth
-
Nicolas, Kenneth;
I think Michaels example in the first post in this topic was not best for what he was trying to explain. If TableA had 3 rows, he would want the 3 rows from TableA to be returned, but not have any of these 3 rows "duplicated" because of multiple records in TableB were joined (but still return values from the first matching row in TableB. So (I had assumed) he would want more than just the 1 row a "TOP 1" would returned.
This problem probably arises because the tables were not normalized correctly/completely. But, the situation arises often because a full normalization would be inefficient. For instance, he may be querying a OLAP-type or datawarehouse database where some data is duplicated on rows for performance reasons. It is not easy doing this, and performance will be a problem. But this often comes up in designing queries.
Mark -
Actually, I ran into a problem very similar that wasn't because of normalization. I had to return customer info along with info from their first order in a single statement (or cheapest item in the order, etc.). This technique was very useful for that problem.
-
Perhaps a small example of the two tables, a few rows data and the desired output, illustrating the problem would clear things up then.
/Kenneth
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply