MS SQL Server :: Select Top, Group By

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

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

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 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 N, Group By Query

Nov 16, 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 results I want using UNION, but this is not practical when there is more than a few groups. I think it should be possible using a derived table, but I am not sure how to do it! 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 3 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 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

Select Top N Results In A Group; Count(*)&<=n

Jan 18, 2003

Two tables: CompanyPrices(CompanyID, ProductID, Price), CompanyRegion(CompanyID, Region)
ProductID is the primary key.

I want to get 10 smallest prices in each Region. In other words, I am looking for 10 cheapest prices in each region. So, if there are 20 regions, I should get excatly 200 rows having prices for products from 200 companies if there were at least 10 companies in each region.

I tried the follwoing, but get incorrect results.

select S1.Region, S1.Price from (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S1 inner join
(select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S2 on S1.Region = S2.Region
group by S1.Region, S1.Price having count(*)<=10 order by S1.Region, S1.Price

However, if I want to get 10 cheapest products for each company, the above sql works by modifying the join condition. Instead of S1.Region = S2.Region , I use S1.CompanyID = S2.CompanyID and I get correct results for 10 cheapest products for each company.

select S1.CompanyID, S1.Price from (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S1 inner join
(select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S2 on S1.CompanyID = S2.CompanyID
group by S1.CompanyID, S1.Price having count(*)<=10 order by S1.CompanyID, S1.Price

I am not sure what is wrong in the first query and why it does not work when the second one works. Could someone help in making the first query work to give me correct results?

View 2 Replies View Related

How To Select Last Rows In Group By Clause

Nov 7, 2008

I have a table which stores datewise Transactions of different items. Fields and sample data is

RecID, ItemID, Date, Received, Issued, Stock
1, 5, 11-03-08, 10, 10
2, 5, 11-05-08, 3, 7*
3, 8, 11-15-08, 25, 25
4, 8, 11-16-08, 8, 33
5, 8, 11-18-08, 6, 27*

Now i want to select last row for each item (indicated by *). Is it possible in one single statement.

View 6 Replies View Related

Adding New Column To Group By - Select

Feb 25, 2015

I´m looking to create a select where I sum the daily_return by stock_code, and then I would like to have an additional column containing the most recent "rating" available by date

So if you where to execute the query below, the result would be:

stock1 0.54 3
stock2 0.05 1

Here is what I have so far:

DECLARE @stock_returns TABLE
(
stock_code VARCHAR(10) NOT NULL,
date1 DATE NOT NULL,
daily_return NUMERIC(10, 2) NOT NULL,
rating numeric (6,0) not null);

[Code] .....

View 2 Replies View Related

Select The Last Record Group By? Ident....

May 13, 2007

Hello

I have a table from cars GPS positions:
CREATE TABLE Positions (
Ident VARCHAR(20) NOT NULL,
Valid BOOL,
Date DATETIME NOT NULL,
Latitude FLOAT,
Longitude FLOAT,
Speed INT
}

Sorry about the poor english... :)
I like select the last position where Ident=DEMO1 and DEMO2....
How can I make that?
Practically I sould like to now, where is the last positions of selected cars!
I try this, but not good:
SELECT
MAX(date),
ident,
latitude,
longitude,
speed
FROM
positions
WHERE
valid=1 &&
(ident='DEMO1' || ident='DEMO2'|| ident='DEMO3')
GROUP BY
ident
;

I get 3 record, the dates is the last, but the lat. and long. is wrong...

Thanks,
Laci

View 5 Replies View Related

How To Select Top N Number Per Date Group?

Sep 12, 2006

I have 1 table named BookMe,

with fields

$Date, $Div, $Name

I want to select the top N number of Div's (ie highest Div value) per distinct Date.

i know how to get the distinct date like this:

SELECT DISTINCT $Date FROM BookMe ORDER BY $Date

How do I combine a TOP keyword to get the TOP n rows per distinct date???

Thx!

View 5 Replies View Related