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
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
