Top 2 Entries For Each Group FLD1,FLD2,FLD3 (was SQL SELECT Statement)
May 18, 2006
Dear All,
I have the following set of data, where FLD1, FLD2...FLD4 are the fields.
I want to be able to select for each group FLD1,FLD2,FLD3 ORDER BY FLD4 ASC the top 2 entries
Example :
FLD1 FLD2 FLD3 FLD4
ABC XXX 1 98
ABC XXX 1 81
ABC XXX 1 5
ABC XXX 2 1
ABC XXX 2 9
ABC XXX 2 2
BBC XXX 2 2
The result I want is :
ABC XXX 1 5
ABC XXX 1 81
ABC XXX 2 1
ABC XXX 2 2
BBC XXX 2 2
Is there a way to do it using SQL?
Thanks in advance
View 4 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
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
Nov 29, 2006
I have a complicated problem, and I'm new to SQL so any help would be greatly appreciated.
I am creating an export file (fixed width) that contains a breakdown of items on an invoice, and each "export file" can contain many invoices. The problem is that I need to apply an incremental "invoice" count on each line. This isn't as simple as doing a running sum of "1" on each record, because the first 5 rows may all be on the same invoice, and all rows need to be identified as being associated with "invoice 1". The next invoice will be known as "invoice 2" and again may contain many rows, all requiring "invoice 2".
Does this make sense?
EG.: I am shipping products, and the breakdown is: Vessel, Voyage, Invoice No, Product, Mark.....
SAGMIR 025 001 HEM/FIR HLF550...
SAGMIR 025 001 HEM/FIR KILN-D HLF505...
SAGMIR 025 002 HEM/FIR HLF660....
The SQL statement that produces the above is a Select query with a grouping on VES/VOY/BL_ID/PRO/MARK where the "BL_ID" indicates they are on the same invoice, but is not the incremental number I require. Complicated, i know...
Thanks in advance for anyone who can help.....if this explanation isn't clear please tell me!
Michael
View 3 Replies
View Related
Oct 15, 2004
heyas,
i wanna know how can i select only entries with at least 2 rows?
What i mean is that i want to make a select * from table1 where columnA = 'Something' order by columnB
but addicionally i want it to return only register that happen at least 2 times for columnB
thanks in advance!
View 2 Replies
View Related
Mar 13, 2012
I currently have a stored procedure that selects sales entries from a table and inserts them into a temp table. To execute this procedure you must enter the store number and the dates required.
What I am looking to do is run this procedure daily for one store daily to load the previous days sales into the temp table.
I have tried using the following
DECLARE @dt DATETIME
SET @dt = CAST(CAST(MONTH(getdate())as varchar) + '-' +
Cast(DAY(GetDate()-1)as varchar)+ '-' +
Cast(YEAR(GetDate())as varchar)as DateTime)
set @sql = @sql + ' and hd.TXHD_START_DATE between ''''' + @dt + ''''' and ''''' + @dt + '23595999+9999'''''
But i am getting an error
Syntax error converting datetime from character string.
View 4 Replies
View Related
Mar 7, 2006
Hi AllStrange request I know, but could somebody give me pointers on how I can put3 queries into 1 'thing' and then get only the unique entries from this'thing'.To explain, I'm using Excel/VBA/ODBC to query an SQL DB. The 3 queriesthemselves aren't that complex and all return the same 2 fieldsets of stockcode and stock desc. Because these separate queries might bring back thesame stock code/description I need to amalgamate the data and then queryagain to bring out only distinct stock values, eg:Query 1 brings back:stock code stock descIVP Invoice PaperSTP Statement PaperKGC Keyboard Coveretc... etc...Query 2 brings back:stock code stock descIVP Invoice PaperBOB Back PackKGC Keyboard Coveretc... etc...Query 3 brings back:stock code stock descKGC Keyboard Cover3.5"D 3.5" Disksetc... etc...I need to produce 1 resultset that shows:stock code stock descIVP Invoice PaperBOB Back Pack3.5"D 3.5" DisksKGC Keyboard CoverSTP Statement Paperetc... etc...(all unique entries)I'm currently just bringing back the 3 query results in Excel, but I'd liketo be able to do the above.In light of I'm using Excel/VBA/ODBC on a PC, is it possible to do?ThanksRobbie
View 1 Replies
View Related
Sep 3, 2007
Hello... im having a problem with my query optimization....
I have a query that looks like this:
SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)
it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View 3 Replies
View Related
Dec 2, 2013
If I wanted to search for Jobs as a particular status (e.g. 0130) and wanted to keep the jobs at this status until it has reached 0500, 0125, or 0900 in it's subsequent status log entry, how can I write the SQL for it to achieve it?
I have the following SQL which searches for the Jobs at 0130, but don't know how to develop it further to search on the requirement above.
------ SQL -------
SELECT
job.job_number,
(SELECT MAX(jsl.job_log_number)
FROM job_status_log jsl
WHERE
job.job_number = jsl.job_number AND
jsl.status_code = '0130') as Last_Early_Warning_Status_Entry
[code].....
In the job_status_log table above, there is a job_log_number field which increments by 1 when there is a new status log entry.
View 1 Replies
View Related
Aug 29, 2006
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies
View Related
Jan 9, 2015
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
View 4 Replies
View Related
Jul 20, 2005
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View 2 Replies
View Related
Oct 29, 2007
Hi guys,
I have the query below (running okay):
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:
Field02
----------------------
22810
8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760
22810
Is there any good suggestion?
Thanks in advance for any help,
Aldo.
View 3 Replies
View Related
Jul 4, 2006
Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E
can any one help me in this?
please give me a sample query.
Thanks and Regards,
Kiran Suthar
View 7 Replies
View Related
May 5, 2015
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
UPDATE TABLE1
SET TABLE1.FIELD2 = 1
ELSE
UPDATE TABLE2
SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
View 7 Replies
View Related
Oct 17, 2007
hi everyone,
I have a little problem concerning a SQL statement I need to do.
here is my statement:
SELECT Location, [Week of Availability], GSDC_Name, PA_Name, CA_Name, MAX([Year of Availability]) AS [Year]
FROM dbo.tbl_availability
GROUP BY Location, GSDC_Name, PA_Name, CA_Name, [Week of Availability]
now the problem in this statement is that I don't want to group by Week of Availability, but I do want to select it. BUT, if I remove Week of availability from the Group by part, I get an error! how can I do this?
View 6 Replies
View Related
Mar 23, 2006
A client wants to keep track of the number of searches for keywords ina date range. So, I'm storing each occurance of a search in a table.The columns are:PK: idsearch_stringsearch_dateI'm trying to wrap my head around how I would select the number ofoccurances for each string, divided by days. The desired result wouldlook something like:search_date search_string numOccurances----------------------------------------------------------March 20 dogs 4March 20 pigs 2March 21 dogs 8March 22 pigs 3March 22 pigeons 5I've tried a query like:selectsearch_string, CONVERT(CHAR(11),search_date,106) as search_date,count(search_string) as numOccurancesfrom searcheswhere search_date >= dateadd(d,-3,getdate())group by search_date, search_stringbut it doesn't give me the desired results. I'm sure I'm just lookingat it the wrong way.Suggestions?Thanks!
View 2 Replies
View Related
Jul 20, 2005
I need this to work:SELECT [ID] FROM [test] WITH (NOLOCK) where [a/c/d]='a' GROUP BY [unit#],[EFF DATE] HAVING COUNT ([unit #]) > 1The problem is that I get an error that [ID] needs to be in the GROUPBY clause or aggregate function. if I put it in there, I will get noduplicates (because it is the identity field). The whole point ofthis is to find dups.Thanks for any help.Robby
View 3 Replies
View Related
May 19, 2008
I am working on some sql for a report and i am getting an error with the group by statement.
SELECT Member.memberNo, Member.FamilyName, Member.GivenName, Member.gender, Membership.FeesPaid, (Membership.Fees - Membership.FeesPaid) AS OutstandingFees FROM Membership
INNER JOIN Member ON Member.memberNo = Membership.memberNo
GROUP BY Member.registrationNumber;
This fails because of the GROUP BY.
Does anyone know why this is the case?
This is the error that is produced:
SELECT DISTINCT Member.memberNo, Member.FamilyName, Member.GivenName, Member.gender, Membership.FeesPaid FROM Membership
*
ERROR at line 1:
not a GROUP BY expression
If someone could help this would be greatly appreciated.
cheers,
View 3 Replies
View Related
Oct 20, 2014
In the below code i want to use select statement for getting customer
address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname
from customer table.Rest of the things will be as it is in the following code.How do i do this?
INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,
[code]....
View 1 Replies
View Related
Aug 10, 2006
I have 3 tables, with this relation:
tblChats.WebsiteID = tblWebsite.ID
tblWebsite.AccountID = tblAccount.ID
I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:
SELECT * FROM tblChats c
LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID
LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID
WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
View 1 Replies
View Related
Jan 24, 2008
Hello everyone,
I'm trying to practice some SQL statements from my Web Data Administrator. Until now everything has worked fine. However, when I write the following statement:
SELECT DATEPART(m, Date) + '/' + DATEPART(yyyy, Date) AS Month, COUNT(*) AS NumberOfEvents FROM CompanyEvents GROUP BY DATEPART(m, Date) + '/' + DATEPART(yyyy, Date)
I get the following error " Server: Msg 245, Level 16, State 1, Line 1Conversion failed when converting the varchar value '/' to data type int."
Do I have to change something on the database?
Now, I tried to google the msg 245 and I cannot get any clear answers on the topic.
I would greatly appreciate if someone could give me some guidance on this problem.
Thanks,
Eduardo
View 2 Replies
View Related
May 21, 2008
I have the following SQL Statement:
SELECT CONVERT(char(10), FixtureDate, 101) AS Date, COUNT(*) AS 'NumberOfRecords'FROM tblFixturesGROUP BY CONVERT(char(10), FixtureDate, 101)
I want to add a new column called "need results".
This column needs to be count if a certain cell is NULL.
Count If HomeScore IS NULL
as well as grouping by date and counting the number of records. So the third column needs to count the number of records where homescore IS NULL
View 1 Replies
View Related
Apr 12, 2004
Hi,
I currently have a table whose DDL is as follows:
CREATE TABLE [tblSales] (
[OrderID] [int]
[SaleDate] [smalldatetime] ,
[ProductCode] [nvarchar] (255) ,
[QtySold] [float] ,
[UnitPrice] [float] ,
[Discount] [float] ,
[GrossSaleAmount] NULL ,
[NetSaleAmount] [float]
)
The GrossSaleAmount and NetSaleAmount are calculated fields. But for this post, kindly ignore why I am storing calcuated fields...
QUESTION:
What I want to do is to populate another table (the DDL of which is give below) from tblSales in such a manner that the TOTAL sales from each product for each available
date is grouped/summed together.
CREATE TABLE [tbl_Product_Grouped_Sales] (
[SaleDate] [smalldatetime] ,
[ProductCode] [nvarchar] (255) ,
[TotalQtySold] [float] ,
[NetSaleAmt] [float]
)
Thanks in advance for your help.
View 1 Replies
View Related
Oct 26, 2012
I want to run a much larget SQL statement, but for examples sake this is a good starting point
Code:
Select efName, elName, eAddress, SUM(Convert(money, bonus1)+Convert(money, bonus2)+Convert(money, bonus3)) As TotalBonus, ePay FROM tableEInfo
It is telling me that I have to use Group By, but the problem is that most of my fields are text fields, which it looks like have to be converted in order to use with a group by statement. Is it possible to use the sum function with no group by statement?
View 13 Replies
View Related
Apr 8, 2014
I recently had an interview and the interviewer has asked me to use a group by clause in an update statement. Here is the table and data:
Table name: Customer
cust Size Geo Segment
BP LG EU
Accenture LG US
MSC MED US
SclumbergerLG US
So the request is to update the Segment column with a unique number starting with number 1.
So for BP it should be 1, Accenture it should be 2, MSC it should be 3, Sclumberger should be 1 since the size and geo are not unique. He asked me to use a group by size and geo to generate a unique number for each combination and than make the update.
View 2 Replies
View Related
May 13, 2007
Hi guys,
I am having a little diffulty displaying two columns next to each other in a case/group by statement as code shown below.
I was wondering if i could have the results displayed such that the gst_amount and total_amount are in two separate columns (as they currently are) however the results of the rows are in the same row not in separate rows (as they are currently). I dont think i can do this in the current case statement that i have (i.e: the two case statements).
Any feedback would be appreciated
SELECT distinct
PERIOD.STARTDATE,
temp_111.EVENTTYPEID,
case when temp_111.[name] like '%GST%'
then sum(temp_111.CONTRIBUTIONVALUE)
end as GST_AMOUNT,
case when temp_111.[name] not like '%GST%'
then sum(temp_111.CONTRIBUTIONVALUE)
end as Total_Amount
FROM temp_111 INNER JOIN PERIOD
ON temp_111.PERIODSEQ = PERIOD.PERIODSEQ
WHERE
(NOT temp_111.PRODUCTID = 'IIIE' OR temp_111.PRODUCTID IS NULL)
AND temp_111.PERIODSEQ in ('111')
group by PERIOD.STARTDATE,
temp_111.EVENTTYPEID,
temp_111.[name]
Here is the current result displayed:
Startdate eventtypeid gst_amount Total_amount
2006-11-01 00:00:00.000NelNULL 83470.5608000000
2006-11-01 00:00:00.000NelNULL 161408.5264874810
2006-11-01 00:00:00.000NelNULL 677568.2683000000
2006-11-01 00:00:00.000NelNULL 2645478.1215092400
2006-11-01 00:00:00.000Nel8347.0560800000 NULL
2006-11-01 00:00:00.000Nel16140.8526488160NULL
2006-11-01 00:00:00.000Nel67756.8268300000NULL
2006-11-01 00:00:00.000Nel264547.8121507070NULL
Instead I want the result to show something like this:
Startdate eventtypeid gst_amount Total_amount
2006-11-01 00:00:00.000Nel8347.0560800000 83470.5608000000
2006-11-01 00:00:00.000Nel16140.8526488160 161408.5264874810
2006-11-01 00:00:00.000Nel67756.8268300000 677568.2683000000
2006-11-01 00:00:00.000Nel264547.8121507070 2645478.1215092400
View 2 Replies
View Related
Sep 2, 2007
Hi Experts, I would like to make a stored procedure in my db: I have sql express 2005.
I get Error 164 when creating this procedure:
CREATE PROCEDURE CrossTable
@Variable1 smallint,
@Variable2 smallint,
@Value smallint
AS
BEGIN
SELECT @Variable1, COUNT(@Variable1) AS 'Haufigkeiten'
FROM SurveyData
WHERE @Variable2 = @Value
GROUP BY @Variable1
END
GO
I would like to generate a frequency chart per userdefined-variable (@variable) with a where restriction.
The GROUP BY @Variable1 seems to be problem: is there some workaround in order to use variables in a GROUP BY clause? or how can I write an sql statement which do the same as this procedure (CrossTable) without using the GROUP BY clause?
Thanks a lot for your replies
Greets from Switzerland
Chris
View 3 Replies
View Related
Mar 20, 2007
Hi,
I am having trouble getting my query right. i am using a stored procedure to retrieve this data and bind it to my grid view.
Problem: I can't associate a field with a column that i am returning with my record set.
Details: I have query that i want to return 9 columns (UserName, Pre-Approved, Processing, Underwriting, Conditioned, Approved, Docs Out, Docs Back, Conditions Met). The username column lists all loan agents. I want the other 8 columns to list the name of the borrower (crestline..borrower.lastname) that is associate with that loan agent and that loan state. Each time a record is found where there is a valid loan agent (UserName) that meets the 'where' conditions, there will be a borrower. the 'LoanKey' field is a primary key that lets me get the borrower name from the borrower table. I dont know how to construct my query such that those borrower names get associated with their respective column header.
if the query works, my Gridview should look like this ('Name' refers to the borrower name)
UserName | Pre-Approved | Processing | UnderWriting | Conditioned | Approved | Docs Out | Docs Back | Conditions Met
Bob | | | | Name | | | |
Bob | | Name | | | | | |
Bob | | | | | | | Name |
Steve | | | Name | | | | |
Steve | | | | | | Name | |
Here is my sql call:
SELECT cfcdb..users.username, crestline..borrower.lastname,CASE WHEN crestline..loansp.LoanStatus='Pre-Approved' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Pre-Approved',CASE WHEN crestline..loansp.LoanStatus='Processing' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Processing',CASE WHEN crestline..loansp.LoanStatus='Underwriting' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Underwriting',CASE WHEN crestline..loansp.LoanStatus='Conditioned' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Conditioned',CASE WHEN crestline..loansp.LoanStatus='Approved' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Approved',CASE WHEN crestline..loansp.LoanStatus='Docs Out' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Docs Out',CASE WHEN crestline..loansp.LoanStatus='Docs Back' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Docs Back',CASE WHEN crestline..loansp.LoanStatus='Conditions Met' THEN crestline..loansp.LoanStatus ELSE NULL END AS 'Conditions Met'FROM cfcdb..usersinner join (crestline..loansp inner join crestline..borrower on crestline..loansp.loankey = crestline..borrower.loankey)on crestline..loansp.fstnamelo=cfcdb..users.firstname AND crestline..loansp.lstnamelo=cfcdb..users.lastnameinner join cfcdb..users_roleson cfcdb..users.username = cfcdb..users_roles.usernamewhere cfcdb..users.active = 1 AND cfcdb..users_roles.groupid = 'agent'AND crestline..loansp.enloanstat <> 'Closed' AND crestline..loansp.enloanstat <> 'Cancelled' AND crestline..loansp.enloanstat <> 'Declined' AND crestline..loansp.enloanstat <> 'On Hold'order by cfcdb..users.username asc
View 2 Replies
View Related
May 15, 2007
I've got a query where i need to return a max value based on a select but one of the fields i need to return in the results is the records primary key ID No. This messes up the MAX bit and means that all results are returned, not just the max one.
The query i'm using is very long so i've simplified what i mean by the example below. Say i have a table 'Fruits':
ID FruitName Cost1 Apple 0.452 Apple 0.633 Apple 0.524 Pear 0.895 Pear 0.83
And run the query:
select max(Cost),FruitName From Fruitsgroup by FruitName
It'll correctly return:
FruitName CostApple 0.63Pear 0.89
Now i need the ID also returned by my query so i go:
select max(Cost),FruitName,ID From Fruitsgroup by FruitName,ID
This doesnt return the above results with the ID appended to it, it instead returns:
ID FruitName Cost1 Apple 0.452 Apple 0.633 Apple 0.524 Pear 0.895 Pear 0.83
As the ID is always distinct and therefore messes up the grouping. How in this instance would i return the correct result of:
ID FruitName Cost2 Apple 0.634 Pear 0.89
Thanks.
View 9 Replies
View Related
Jul 23, 2006
Hi,
Suppose a table [Inventory]:
Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 10
Table Red 20
Table Yellow 30
Chair Blue 40
Chair Red 50
I'm wondering if there is a group state like this:
Select Item, ?Function(Color), Sum(Quantity) From Inventory Group by Item
which returns this:
Table Blue,Red,Yellow 60
Chair Blue,Red 90
Does anyone has an idea how this can be achieved?
Regards,
Manolis Perrakis
View 10 Replies
View Related
Jul 20, 2005
I've always been mistified why you can't use a column alias in the group byclause (i.e. you have to re-iterate the entire expression in the group byclause after having already done it once in the select statement). I'mmostly a SQL hobbiest, so it's possible that I am not doing this in the mostefficient manner. Anyone care to comment on this with relation to thefollowing example (is there a way to acheive this without re-stating theentire CASE statement again in the Group By clause?):Select 'Age' =CaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))end,max(SubmittedOn), COUNT(SCRID) AS NbrSCRsFrom SCRViewWHERE(StatusSort < 90) ANDCustomerID = 8 andUserID = 133group byCaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))endOrder by max(submittedon) descThanks,Chad
View 4 Replies
View Related
Sep 25, 2015
i have this schema :
CREATE DATABASE ANDEB
USE ANDEB
CREATE TABLE TDocHeader
(
CustName VARCHAR(50) NOT NULL,
DocNum INT NOT NULL,
col1 varchar(50),
[code]...
How i can have a group by for last docnum for Customer and all columns?
View 6 Replies
View Related
