473,320 Members | 1,858 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Get one row from each group of rows

Hi,

I'm trying to build a query that get only one row from a group of
rows, but I need the values from that row and not the results of one
function group.
I need one row for each idRef, with column2=2 and the bigger column1

id |idRef | column1 | column2
1 1 0 1
2 1 1 2
3 1 2 1
4 2 0 1
5 2 1 2
6 2 2 1
7 2 3 2

For these, I will take the rows with id=2 and id=7.

Thank you, and sory for my english.

Jul 22 '07 #1
2 2869
On 22 Jul, 05:19, deluca.vice...@gmail.com wrote:
Hi,

I'm trying to build a query that get only one row from a group of
rows, but I need the values from that row and not the results of one
function group.
I need one row for each idRef, with column2=2 and the bigger column1

id |idRef | column1 | column2
1 1 0 1
2 1 1 2
3 1 2 1
4 2 0 1
5 2 1 2
6 2 2 1
7 2 3 2

For these, I will take the rows with id=2 and id=7.

Thank you, and sory for my english.
The following assumes that there is only one row where column2 = 2 and
column1 is the largest value - as would be the case if (idRef,
column1, column2) was a key for example. If you include DDL with keys
in future posts then people who respond won't have to guess which
columns are unique.

SELECT id, idRef, column1, column2
FROM tbl AS t1
WHERE column2 = 2
AND column1 =
(SELECT MAX(column1)
FROM tbl
WHERE idRef = t1.idRef
AND column2 = 2);

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

Jul 22 '07 #2
On Jul 22, 9:19 am, deluca.vice...@gmail.com wrote:
Hi,

I'm trying to build a query that get only one row from a group of
rows, but I need the values from that row and not the results of one
function group.
I need one row for each idRef, with column2=2 and the bigger column1

id |idRef | column1 | column2
1 1 0 1
2 1 1 2
3 1 2 1
4 2 0 1
5 2 1 2
6 2 2 1
7 2 3 2

For these, I will take the rows with id=2 and id=7.

Thank you, and sory for my english.
select a.* from tbl a
join
(select idref,max(column1) as column1
from tbl
where column2 = 2
group by idref) as b
on a.idref = b.idref
and a.column1 = b.column1

Jul 23 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: rodrigo | last post by:
I have a Asp.net table control that I dynamically add rows from a SQL database. Inside the table, I add radiobuttons and they all have different ID numbers according to BindChain() Looking In...
5
by: Ross Presser | last post by:
The purpose, as you can probably guess, is to produce a set of sample documents from a large document run. The data row has a CLUB column and an IFC column; I want a set of samples that contains...
4
by: David Link | last post by:
Hi, Why does adding SUM and GROUP BY destroy performance? details follow. Thanks, David Link s1.sql: SELECT t.tid, t.title, COALESCE(s0c100r100.units, 0) as w0c100r100units,
5
by: Roy Gourgi | last post by:
Hi, Is there a way to group rows by some columns in such a way that I can clearly see them in a distinguished fashion when I look at a view or table. This is my code below but when I try using...
1
by: heckstein | last post by:
I am working in Access 2002 and trying to create a report from our company's learming management system. I am not a DBA and most of my SQL knowledge has been self taught through trial and error. I...
3
by: igotyourdotnet | last post by:
I'm using a gridview and I have it formatted using sub headings. now I need to get totals for each sub heading. Is this possible? example: BMW (sub heading) 25,000 14,252 25,000 total:...
2
by: alomrani | last post by:
Hi all , I'd appreciate anyone insight on this I have created a form that fetched rows from a query. On each row I have added buttons to execute command for that relevant row only. The Form...
2
by: eloi-ribeiro.blogspot.com | last post by:
Hi, I'm having a problem with a query. I'm running Access 2000 and a table with 35000 rows. The table contains several visits to the spots with a CODE (spot code) and DATE (visit date)...
0
by: madhavi123 | last post by:
Hi All, I have to create a report in sql server 2005 and I this happens to be my first project in SQL Server. The report has to display a sum for each group and at the end display for all the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.