MS SQL Server :: Select Top N, Group By Query

Select Query By Group

Jun 20, 2008

Hello All
I have table with 3 columns and Datas like this
LRNo              LRAmount   LRType
L001                100                TP
L002                250                PA
L003                150                AC
L004                275               TP
L005                400                PA
L006                125                TP

I need a query to get output like this

LRtype      Count           TotalAmount
TP               3                    500
PA               2                   650
AC              1                   150

Where count is total no of records grouped for LR Type and totalAmount is Sum of TotalAmount grouped by LRType

please help me
Thanks in Advance

View 4 Replies View Related

Select Top N, Group By Query

Nov 14, 2005

Hi All,

I am trying to return a result set that gives me the top 20 results for EACH group (PERILNAME in this case), not the top 20 results of the whole result set.

I have been able to create the resluts I want using UNION, but this is not practical when there is more than a few groups. Below is the code the returns the results I am after, any ideas how to achieve this another way?

SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT) AS [Total Value],
COUNT(loc.LOCID) AS [Num Locs],loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Earthquake'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Windstorm'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Tornado/Hail'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Flood'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
ORDER BY LookupPeril.PERILNAME, [Total Value]

TIA.

View 4 Replies View Related

Select Top 3 By Group Query

Jun 20, 2006

Hi

I need some help in returning the top 3 records in each group ie I have a table containing a list of stores with the address details and I need to return the top 3 records in each postcode area. The Postcode field contains the postcode area ie BN1, BN2 etc. Many thanks.

View 3 Replies View Related

Select Latest Records From GROUP BY Query

Feb 26, 2014

I have a table T (a1, ..., an, time, id). I need to select those rows that have different id (GROUP BY id), and from each "id group" the row that has the latest field 'time'. Something like SELECT a1, ..., an, time, id ORDER BY time DESC GROUP BY id. This is the wrong syntax and I don't know how to handle this.

View 3 Replies View Related

How Do I Make 30 Sec Running Query (select C1 Sum(x) From T1 Where C1 &&> 1000 Group By C1) Run Faster?

Aug 10, 2007

It seems when I run the query with the set staticts IO on then statistic reports back with the 'work table', and the query takes 30+ sec. if the worktable is ommited(whatever the reason?) the query take less 1 sec.

Here is my take, I believe work table is created in tempdb...and if not then whole query is using the cached page, am I right?

if I am right then the theory is, if I increase the (via sp_configure) server min memory setting and min query memory, the query ought use the cached page and return in less 1 sec. (specially there is absolutely no one but me on the server), so far I can't make it go faster...what setting am I missing to make it run faster?

Another question is if the query can not avoid but use the tempdb, is it going to always be 30 sec+ time? why is tempdb involvement make it go so much slower?

Thanks in for you help in advance

View 1 Replies View Related

Efficiency Of A 'SELECT TOP' Style GROUP BY Query: FREETEXT Vs. FREETEXTTABLE

Aug 11, 2007

Hi,

Please have a look at the following two queries, the purpose of which is to find which ten users (represented by 'Username') have created the most records which contain the term 'foo':

SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options

WHERE FREETEXT(*, 'foo')

GROUP BY Username

ORDER BY [Count] DESC

SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options

JOIN FREETEXTTABLE (Options, *, 'foo', 500) ct

ON OptionID = ct.[KEY]

GROUP BY Username

ORDER BY [Count] DESC

They both produce the same result set. However, I am wondering which is more performant. At first glance, it would seem the first one would be. It doesn't involve a JOIN and should, therefore, be more efficient.

But this depends on how the FREETEXT expression is evaluated. My concern is that internally, SQL Server would generate an entire recordset based on 'WHERE FREETEXT(*, 'foo')', which could be thousands of records, and only then restrict this to the TOP 10 by COUNT.

If this does happen, then it would be better to join to a FREETEXTTABLE, where I can at least restrict the result set using the 'top_n_by_rank' parameter (which is set as '500' in this case, as this seems a good balance of performance against the likely number of duplicates I will get in my FREETEXTTABLE results).

So... I am worrying about this unnecessarily? Should I just use the simpler first version?

Any thoughts appreciated.

Thanks

View 3 Replies View Related

SQL Server 2012 :: Obtaining A Comma Delimited List For Each Group In The Output Of A Group By Query?

Jan 10, 2014

I'd like to ask how you would get the OUTPUT below from the TABLE below:

TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B

OUTPUT:

category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6

The code would go something like:

Select category, count(*), .... as id's
from TABLE
group by category

I just need to find that .... part.

View 3 Replies View Related

Select Statement Problem - Group By Maybe Nested Select?

Sep 17, 2007

Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt

View 2 Replies View Related

Return The Results Of A Select Query In A Column Of Another Select Query.

Feb 8, 2008

Not sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?

View 9 Replies View Related

SELECT Using GROUP BY - Please Help Me, Please

Jan 5, 2008

hello,
i have two tables: Pictures and UserComments, both have PictureID column
in second table i store each comment made by users at a specific picture like so: CommentID, UserName, PictureID, Comment, Date
i am trying to make a stored procedure with @UserName input parameter witch returns Distinct Pictures whereon that user has commented (sorry for that whereon expression, is from dictionary and i don't know if it express what i want to mean) SELECT Pictures.OwnerName AS 'Owner', Pictures.Name AS 'Name of picture', COUNT(UserComments.PictureID) AS 'Comments made', Pictures.Image1
FROM Pictures INNER JOIN
UserComments ON Pictures.PictureID = UserComments.PictureID
WHERE (UserComments.UserName = @UserName)
GROUP BY UserComments.UserName, Pictures.Name, Pictures.OwnerName, Pictures.Image1
if i use this statement it shows me the picture details whereon that user has commented and if he commented more than once on the same picture the result isn't duplicated
but with that statement i can't order by UserComments.Date because i have to use GROUP BY UserComments.Date and the results will not be shown in pairs
How can i order the results by Date Desc?
sorry for my bad english, if you don't understand please tell me and i'll try to explain by examples
please help me, thanks

View 4 Replies View Related

Select Top, Group By

Feb 7, 2008

Hello!
This is my query:SELECT tblMessages.id, tblMessages.txtIngress, tblMessages.strSubject, tblMessages.txtMessage, tblMessages.postedBy, tblMessages.datePosted,
tblMessages.intMessGroup
FROM tblMessages INNER JOIN
tblUsersToGroups ON tblMessages.intMessGroup = tblUsersToGroups.belongsToGroup INNER JOIN
tblUsers ON tblUsersToGroups.userId = tblUsers.id
WHERE (tblUsers.userName = @strUserName)
GROUP BY tblMessages.intMessGroup, tblMessages.id, tblMessages.txtIngress, tblMessages.strSubject, tblMessages.txtMessage, tblMessages.postedBy,
tblMessages.datePosted
ORDER BY tblMessages.intMessGroupWhat I want to do is select the top 3 from each intMessGroup. In other cases where I'd want to Group By a computed column I could have used Rank, but since the column intMessGroup contains static values (message group id's) there should be some easy peasy way to do this, or?Thanks beforehand for any and all replies!Cheers!/Eskil

View 7 Replies View Related

How To Select The Top 1 Row In Each Set Of Group????

Mar 27, 2008

hai guys...am new to sql....plz help me out........ the below is my table structure.....Table Name : #temp pgm_main_category_id         pgm_sub_category_id                filepath                                                                                             17                                                       15                  photo/Writer/Content/Sports/Basketball/bb1.jpg                                                       17                                                       16                  photo/Writer/Content/Sports/Cricket/cricket1.jpg                                                     17                                                       17                  photo/Writer/Content/Sports/BaseBall/base1.jpg                                                       18                                                       18                  photo/Writer/Content/Nature/Forest/forest1.jpg                                                       18                                                       19                  photo/Writer/Content/Nature/Tree/tree1.jpg                                                           18                                                       20                  photo/Writer/Content/Nature/Flower/flower1.jpg                                                       19                                                       21                  photo/Writer/Content/Gadget/Laptop/laptop1.jpg                                                       19                                                       22                  photo/Writer/Content/Gadget/DigitalCamera/camer1.jpg                                                 19                                                       23                  photo/Writer/Content/Gadget/Mobile/cybermbl1.jpg                                                     17                                                       24                  photo/Writer/Content/Sports/Formula1/F1.jpg     from this table i need the query output as below......for example: This is my expected output pgm_main_category_id         pgm_sub_category_id
filepath

17
15
photo/Writer/Content/Sports/Basketball/bb1.jpg
18                                                       18                  photo/Writer/Content/Nature/Forest/forest1.jpg    19
21                  photo/Writer/Content/Gadget/Laptop/laptop1.jpg  17
16
photo/Writer/Content/Sports/Cricket/cricket1.jpg      18
19
photo/Writer/Content/Nature/Tree/tree1.jpg       19
22
photo/Writer/Content/Gadget/DigitalCamera/camer1.jpg    17                                                       17                  photo/Writer/Content/Sports/BaseBall/base1.jpg 18                                                       20                  photo/Writer/Content/Nature/Flower/flower1.jpg 19                                                       23                  photo/Writer/Content/Gadget/Mobile/cybermbl1.jpg              17                                                       24                  photo/Writer/Content/Sports/Formula1/F1.jpg      if anybody have an idea about this query plz send me...... i need this immediately guys.....thanks in advance......regards janu

View 16 Replies View Related

Select, Group By, Sum...

Mar 29, 2006

Hello,I have two tables:Student (containing columns: name, id, status, amount)Uplate (containing columns: id, student_id as foreign key, date, payement)Students make payment from time to time and that payment goes to Uplate.How to list (What is SELECT statement) to getevery studentits idstatusamounttotal payment made till today! (like sum all payement for this student)Thanks in advance.

View 4 Replies View Related

How To Select The First Row For Each Group?

Oct 10, 2004

Hello, everyone:

There is a table as below that has three groups regarding to cus. How to select first cid for each group. Thanks.

zyt

cus cid
ALFKI10643
ALFKI10692
ALFKI10702
ALFKI10835
ALFKI10952
ALFKI11011
ANATR10308
ANATR10625
ANATR10759
ANATR10926
ANTON10365
ANTON10507
ANTON10535
ANTON10573
ANTON10677
ANTON10682
ANTON10856
AROUT10355
AROUT10383
AROUT10453
AROUT10558
AROUT10707
AROUT10741
AROUT10743
AROUT10768
AROUT10793
AROUT10864
AROUT10920
AROUT10953
AROUT11016

View 2 Replies View Related

Select Top 3 By Group

Feb 21, 2007

I need to find the first 3 days that a product is scheduled in one of my
tables. It may be scheduled on 12 different days, but I only want the first 3.

So, based on some sample data and this thread:

http://www.dbforums.com/t1115304.html

I was able to come up with a query that does what I need.
However, I have no idea how it works. For one, what
is Count(*) counting in the query?

create table #tmpSched (
sched_date datetime,
product char(3)
)

insert into #tmpsched
select '1/1/2001', 'abc'
union all
select '1/2/2001','abc'
union all
select '1/3/2001','abc'
union all
select '1/1/2001', 'def'
union all
select '1/2/2001','def'
union all
select '1/1/2001', 'ghi'
union all
select '1/1/2001', 'jkl'
union all
select '1/2/2001','jkl'
union all
select '1/3/2001','jkl'
union all
select '1/4/2001','jkl'

SELECT a.SCHED_DATE, a.PRODUCT
FROM #tmpSched a
INNER JOIN #tmpSched b
ON a.product = b.product
AND a.sched_date > = b.sched_date
GROUP BY a.product, a.sched_date
HAVING COUNT(*) <= 3
order by a.product, a.sched_date

drop table #tmpsched

Any explanation would be appreciated.

EDIT: Wanted first 3, not last 3. Changed "a.sched_date < = b.sched_date" to a.sched_date > = b.sched_date

View 12 Replies View Related

Select TOP N Of Each Group

Sep 8, 2013

I'm trying to find the most effective way to get the top N of each group in a group byI've already try the ROW_NUMBER() OVER PARTITION Method but it is still slow ...I've also checked the CROSS APPLY Method not really more efficient..Bu t My group is very simple and limited to one column...So Now I'm on the way to use a stored procedure make a loop of TOP n for each distinct element of my column

View 1 Replies View Related

Select With Group

May 9, 2006

Hi, on a table with two fields DATETIME and PLATAFORM I need to meka thefollowing queries

One return the total grouped by hour like

'1:00:00.000' - 5 /* Registries from 0:00:00.000 to 1:00:00.000 from the day choose */

'2:00:00.000' - 12/* Registries from 1:00:00.001 to 2:00:00.000 from the day choose */

'3:00:00.000' - 5/* Registries from 2:00:00.001 to 3:00:00.000 from the day choose */

'4:00:00.000' - 12/* Registries from 3:00:00.001 to 4:00:00.000 from the day choose */

and on....

the other is group by week, month(with the 30, 31 days of the month

Like

01/05/2006 -- 5

02/05/2006 -- 5

03/05/2006 -- 5

and year(with the 12 months)

january - 5

february -- 12

What the easiest and best way to do that...

I tried with cursor but it takes too much time to complete.

View 1 Replies View Related

SELECT, MAX(), Group By

Feb 28, 2008

Hi, I have two tables Employee and Department.
Employee Department

-EmployeeID -DepartmentID
-EmployeeName -DepartmentName
-Salary
-DepartmentID

I want to SELECT each departments EmployeeName, who has max salary.

SELECT EmployeeName,MAX(Salary) FROM Employee GROUP BY DepartmentID

I know it won't work. I use CURSOR to solve this. But is there any easy way to do this. Using SELECTs
.

View 5 Replies View Related

Select And Display The Max Value From A Group In Sql?

Mar 29, 2007

hi
i have a database that has multiple records under the same id (each time a record is updated, it's assigned a date_value), i'm wanting one row to be generated using the data from the most recent date_value field.
a sample of the database looks something like this:
ID date_value data1 24 march info11 25 march info2
my statement looks like this:select max(date_value), data from table_name where data is not null group by id
this seems to bring up the following error for all the fields not in the group by part of the code:Column 'COLUMN_NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
what am i doing wrong?
TIA!

View 1 Replies View Related

Select Max() And Group By Problem

Jun 14, 2008

I have this query.
SELECT ForeignKey, MAX(Version) AS Latest_VersionFROM tblContentWHERE (Published = 'True')GROUP BY ForeignKeyORDER BY ForeignKey
It works perfectly just like it is above. It returns 20 records choosing the maximum value in the version column for each value in the ForeignKey column. Here is a sample of the result set.
ForeignKey     Latest_Version
1                    2
2                    2
3                    1
Then when I try to add other fields to the select and group by clauses I hit a problem. Here is the new query
SELECT ForeignKey, MAX(Version) AS Latest_Version, TitleFROM tblContentWHERE (Published = 'True')GROUP BY ForeignKey, TitleORDER BY ForeignKey
Now it is returning 22 records in the result set Here are the relevant records.
ForeignKey     Latest_Version Title
1                    1                    Title1
1                     2                     Title2
2                    1                       Title3
2                    2                       Title4
3                    1                      Title5
I know it is necessary to add each column in the select clause to the group by clause but that seems make group by not work at all. Am I going about this the wrong way. Basically I want to return only the ForeignKey records that have the highest version. This table does have a foreign key relationship with another table. Should I be doing this with a join instead somehow?
Thanks, Laura

View 11 Replies View Related

SELECT COUNT Of MAX (GROUP BY)

Jul 16, 2006

Hello

I want to get the COUNT of

SELECT MAX(id) AS ids, Name, Version, Pack, Serial
FROM Products
GROUP BY Name, Version, Pack, Serial

SELECT COUNT(MAX(id) AS ids) AS countIds, Name, Version, Pack, Serial
FROM Products
GROUP BY Name, Version, Pack, Serial

doesnt work

thank you

View 4 Replies View Related

Select Top 3 And Then Group Remainder

Dec 2, 2011

I have an aggregated table called T1 which looks like this:

Code:

Name Amount
a 10
b -4
c 9
d 5
e 0
f 0
g 8
h 3
i 3

I need to write a query which selects the top 3 names by amount which have an amount greater than 0. The remaining names which have amounts greater than 0 should be grouped together as 'others' at the bottom. Finally I need to add a total row. So the final result I am looking for is:

Code:
Name Amount
a 10
c 9
g 8
others 11
total 38

The part I'm struggling with is how to collapse the remaining names into the 'others' group.

View 2 Replies View Related

Select Last Record By Group?

May 18, 2012

query to show last record/Partner or PartnerName.

select 'PURCHASE' as EntityName, d.PartnerName, h.*
from (
select MAX([TimeStamp]) [Data import], COUNT(1) [Numar de inregistrari], StartDate, EndDate, DistributorId
from DataImport.PurchaseHistory
group by DistributorId, StartDate, EndDate
) h
inner join Partner d on d.PartnerId = h.DistributorId
where d.Active = 1
order by DistributorId, StartDate desc, EndDate desc

View 4 Replies View Related

SQL Select Last Record Per Group

Jan 24, 2004

I'm looking for some sql syntax that will return the last entry per group in a secondary table. MEANING: have 2 tables, one with names and the other with visits. I need to be able to display all the patients with there last visits.

TABLE1 info
ID1 fname1 lname1 DOB1
ID2 fname2 lname2 DOB2
ID3 fname3 lname3 DOB3

TABLE2 info
ID1 Visit2
ID1 Visit3
ID1 Visit1
ID1 Visit4
ID2 Visit1
ID2 Visit2
ID3 Visit1

I need a view or SP to return the following:

ID1 fname1 lname1 dob1 visit4
ID2 fname2 lname2 dob2 visit2
ID3 fname3 lname3 dob3 visit1

It seems like it should be a smiple process, only I just can't get the syntax to work.... Any Help would be GREAT!
thx

View 2 Replies View Related

T-SQL (SS2K8) :: Select MAX Per Group?

Jul 22, 2015

I want to select all the Rows with Grouping done by PARENTID and Max value in REVNR per GROUP.

I am able to get one MAX row per Group but not all the Rows which has MAX value.

Here is my Table sample.

CREATE TABLE #TableA0 (
iINDEX INT
,PARENTID INT
,DOCUMENTID INT
,QTY INT

[code]....

In the result how do i get just 2 ParentIDs but multiple rows of DocumentID.

View 7 Replies View Related

Select One Photo From Each Group

Jul 28, 2013

I have created groups of photos, i want to select one photo from each group, what will be the query?

View 2 Replies View Related

Select One Record From Each Group

Jan 21, 2014

I have for example a table with columns name, surname, id, ..., weight, age. I need to choose from each age group (GROUP BY age) entire record of the person who has the greatest weight. How to construct such a query?

View 1 Replies View Related

Select Records If All In Group Have Same Value

Feb 11, 2015

Here is my table data:

CREATE TABLE
#TestTable (
Pk INT,
GroupID INT,
Enabled BIT

[code]..

I need to write a select query that will retrieve any GroupID in which every record has an Enabled value of 1.In the example I've provided, only GroupID 1 and 3 will be returned since GroupID 2 has a record with an Enabled value of 0.What would be the most efficient way to write such a query?

View 2 Replies View Related

Group SQL Select Statement

Mar 23, 2007

Hi,I am bloody amateure and I was wondering if someone could help me editthe statement below so it groups the field SCDMASTER.SCTY_CLASS_CODEand creates one field called "total balance" equallingCUSTODY_BALANCE.OPENING_BALANCE + CUSTODY_BALANCE.DEPOSIT_AMOUNT -CUSTODY_BALANCE.WITHDRAWAL_AMOUNTSELECTCUSTODY_BALANCE.APPLICATIONCYCLEDATE,CUSTODY_BALANCE.ASSET_ID,SCDMASTER.SCTY_CLASS_CODE,CUSTODY_BALANCE.OPENING_BALANCE,CUSTODY_BALANCE.DEPOSIT_AMOUNT,CUSTODY_BALANCE.WITHDRAWAL_AMOUNTFROMFPMCAPSHIST.CUSTODY_BALANCE CUSTODY_BALANCE,CAPSREPORT.SCDMASTER SCDMASTERWHERECUSTODY_BALANCE.ASSET_ID = SCDMASTER.CUSIP_ID AND((CUSTODY_BALANCE.APPLICATIONCYCLEDATE={ts '2007-03-21 00:00:00'}) AND(SCDMASTER.SCTY_CLASS_CODE Not In('BILL','NOTE','BOND','CD','BD','CB','TINT','TPRN' ,'CA','YD')))Thanks,Andreas

View 3 Replies View Related

How To Select Only The First Record From A Group

Sep 18, 2006

I have a question about selecting only the first record in a group. Example: I have table A with primary key = 999. Table B has multiple records with primary key = 999. How can I match Table A 999 with the first occurrence of 999 in Table B, and then extract other field data (such as street address) from the Table B record. I have tried using the Count() function, but it seems that I can only do this using cursors.

Thanks,

BobD

View 2 Replies View Related

SELECT GROUP BY - T-SQL Vs. MySQL

May 24, 2006

Hi,

I have a problem to get the following query going which works fine in MySQL:

SELECT MAX(id), col2, col3, col4 FROM mytable GROUP BY col3

So, what I want is to select the row with the highest ID from a group of rows with the same value in col3. How would that be done in T-SQL?

Thanks,

Tom

View 8 Replies View Related

Select 1 Row From Each Group In A Single Table

Aug 19, 2006

Hello everyone, I've got a bit of an SQL mystery that I'm not sure how to solve. For some reason I just cant get my head around it. Here's the scenario:
Table A:
_____________
BidID - Int identity
AuctionID - int
BiderName - varchar(50)
bidAmount - money
______________________
Now obviously each Bid will have a Unique ID using BidID but the other rows will contain multiple bids per user, on many different items possibly.
BidID   AuctionID   BiderName   BidAmount
1            4005            joeblow         100.00
2            4005            janedoe         101.00
3            4005            joeblow         107.00
4            4006            joeblow         100.00
5            4006            janedoe         105.00
6            4006            joeblow         106.00

I need to find out which Auctions JoeBlow is bidding on, but I dont need a table with Rows for every single one of his bids, just a distinct auctionID for his top bid so in this case the only thing returned would be
3            4005            joeblow         107.00
6            4006            joeblow         106.00
Any clues? I've been through sub querys, and stored procedures, and I cant get anything to work quite right.
Thanks in advance for your help.

View 4 Replies View Related