How To Select The Row That Has The Maximum Value In One Field
Sep 21, 2006
This has me stumped.
I have a table UnitRateItems with the following columns
ItemID, Description
and table UnitRates with the following columns
ItemID,Year,UnitRate
UnitRateItems and UnitRates have a primary-foreign key relationship on ItemID.
In UnitRates, there may be several rows with the same value for ItemID but a different value for Year. What I want to do is pick the row with the largest value of Year for a given ItemID. I want to do this for ALL the rows in the table (not just for a particular ItemID).
In other words, I want to return one row in UnitRates for each row in UnitRateItems. I want that one row to be the one with the highest Year for that ItemID.
It sounds simple, but I can't seem to figure out how to do it. The MAX function only works on a single column. I can get the MAX Year, but then I can't tell it "pick up the other values that go with the row with the MAX Year".
Any help would be greatly appreciated!
View 5 Replies
ADVERTISEMENT
Sep 16, 2012
I am trying to get the minimum and maximum values from a field in SQL Server 2008 Express, but I cannot even get started because I keep getting this error that I cannot figure out.
View 6 Replies
View Related
Mar 11, 2007
Does the IDENTITY field type in SQL have a maximum size to it?
You know like int only goes so high up,
View 1 Replies
View Related
Feb 27, 2003
Can any one help me, i'm building a dynamic database driven site using dreamweaver and MS SQL2000 andi'm haveing problem storing over 8000 characters in a table filed (IE: it wont let me!!) is there a special table field value that i need to set to get more characters in a table field or is this a limitation of SQL.
Any help or suggestions would be appreciated
Regards
B
View 3 Replies
View Related
Jul 24, 2007
Hi All,
Is there a way to check the data type and the maximum length of a field?
View 1 Replies
View Related
May 31, 2006
I have a problem selecting maximum values inside a data flow, when the field is varchar. For example, I could have an incoming text file with following rows (the real life problem is a bit more complicated, but the idea is the same)
ID Desc
1 Car
1 Truck
1 Bicycle
2 Horse
2 Cow
Now I would like to group by ID, and have the maximum value from the Desc field, resulting
ID Desc
1 Truck
2 Horse
I tried to sort the data flow first with ID ascending and Desc descending, followed by sort with ID ascending and "Remove duplicate sort values" turned on. However, the sort does not seem to select the first Desc value it gets, because the result is.
ID Desc
1 Bicycle
2 Horse
If you have any hints, how to tackle this problem, please help!
View 3 Replies
View Related
Feb 13, 2014
I am using this below query to sum and select maximum values from table. I have converted the cost column here and how can I possibly sum the cost column?
select ID, MAX(Dates) Dates,'$ ' + replace(convert(varchar(100),
convert(money, Cost), 1), '.00', '') Cost, MAX(Funded) Funded from Application group by ID, Dates, Cost, Funded
View 4 Replies
View Related
Dec 19, 2007
Hi All,
Here is my story, how to change a column called Flag_Status based on the maximum Updated date. i.e. i want to make Flag_Status be 1 for the records which have maximum Updated_date (current record) and the rest to make it 0. for example accountID 1 has three records updated, but only one is current the rest are historical, thus i want the history record to be Falg_status 0 and the current record be 1. Note that Inserted_Date and Updated_Date are created using SSIS Derived column During loading the source table, it helps me when each record is inserted into the Data warehouse.
Here is My source table Name: Source_table,
CREATE TABLE dbo.Source_table
(
AccountID INT PRIMARY KEY,
Price int,
Address varchar(30),
added DATETIME DEFAULT GETDATE(),
edited DATETIME DEFAULT GETDATE(),
Flag_Status Int Default 1
)
GO
Here is the Fact_table
CREATE TABLE dbo.Fact_table
(
Fact_table_Key Int Identity (1, 1) NOT NULL,
AccountID INT,
Price INT,
Address varchar(30),
added DATETIME DEFAULT GETDATE(),
edited DATETIME DEFAULT GETDATE(),
editor VARCHAR(64),
Flag_Status Int Default 1,
Inserted_Date DATETIME DEFAULT GETDATE(),
Updated_Date DATETIME DEFAULT GETDATE()
)
GO
Source_table:
AccountID Price Address added edited Flag_Status
---------------- --------- ------------- ----------- ------------- ------------------
1 10 xyz 01-2006 01-2006 1
2 14 abc 02-2006 02-2006 1
3 13 mno 03-2006 03-2006 1
Here is the fact table, table name= Fact_table
Fact_table_Key AccountID Price Address added edited Flag_Status Inserted_Date Updated_Date
-------------------- ------------- --------- ------------ ------- --------- ---------------- ------------------ -------------------
1 1 10 xyz 01-2006 01-2006 1 05-2006 NULL
2 2 14 abc 02-2006 02-2006 1 05-2006 NULL
3 3 13 mno 03-2006 03-2006 1 05-2006 NULL
4 1 17 ght 01-2006 06-2006 1 NULL 08-2006
5 2 18 dmc 02-2006 07-2006 1 NULL 08-2006
6 3 20 kmc 03-2006 09-2006 1 NULL 10-2006
7 1 19 xyz 01-2006 11-2006 1 NULL 12-2006
8 2 19 klm 02-2006 01-2007 1 NULL 02-2007
9 3 21 pqr 03-2006 03-2007 1 NULL 04-2007
Here is what i am thinking: But it gives me Wrong Flag_Status.
UPDATE Fact_table
SET Flag_Status =
CASE
WHEN (SELECT Max(Updated_Date) From Fact_table
WHERE AccountID IN (SELECT AccountID FROM Fact_table
Group By AccountID Having Count(AccountID)>1)) >
(SELECT Max(edited) From Source_table
WHERE Flag_Status = 1)
THEN 1
WHEN (SELECT AccountID From Source_table
Group By AccountID
Having Count(AccountID) =1) =
(SELECT AccountID From Fact_table
WHERE Updated_Date IS NULL)
THEN 1
ELSE 0
END
View 12 Replies
View Related
Jun 11, 2015
I have Database Library, which has a lot of tables and we need 3 tables for quary:
Table Librarians: field ID, field Surname;
Table StudentCard: field ID, foreign key on table Librarians and other fields, which we don't use
Table TeacherCard: field ID, foreign key on table Librarians and other fields, which we don't use
Query: Select the librarian's surname, which gave the most count of books.
I know, how to resolve, when i took datas only from one table, e. g. TeacherCard
SELECT TOP 1 WITH TIES Librarians.LastName, MAX(Librarians.CountOfBooks) AS Books FROM
(SELECT L.LastName, COUNT(*) AS CountOfBooks FROM Libs L, T_Cards T
WHERE T.Id_Lib IN (SELECT L.Id)
GROUP BY L.LastName) AS Librarians
GROUP BY Librarians.LastName
ORDER BY MAX(Librarians.CountOfBooks) DESC
GO
I dont know, how to use datas from TeacherCard and from StudetnCard at the same time.
View 9 Replies
View Related
Sep 28, 2007
Hi,
How to select max of year-month in the Reporting Services. For example I have 3 fields :
1. Year-Month (format : yyyy-mm)
2. City
3. Amount Sales
In the bar chart graph, I have put the City as X-Categories, Amount Sales as Data
And I want to filter only the lastest year month in the database.
Can we do this without going to database and set the filter there ?
Thanks
best regards,
Tanipar
View 4 Replies
View Related
Sep 12, 2015
I have some code I build 2 weeks ago which I’ve been running daily but it’s suddenly stopped working with the following error.
“The table "tbl_Intraday_Tmp" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit” When I google this there seems to be a related to tables with vast numbers of columns.
My table tbl_Intraday_tmp is relatively small. It has 7 columns. 1 of varchar(5), 3 of decimal(9,3) and 2 of decimal(18,0). The bit I’m puzzled with is it was working and stopped.
I don’t recall changing anything but I wouldn’t rule that out. I ‘ve inspected the source files and I don’t believe they have changed either.
DECLARE
@FileName varchar(50),
@Path varchar(50),
@SqlCmd varchar(1000)
= '',
@ASXCode varchar(5),
@Offset decimal(18,0),
[code]....
View 5 Replies
View Related
Apr 20, 2012
I am using MS SQL server 2008, and i have a table with 350 columns and when i m trying to create one more column its giving error with below message -
Warning: The table XXX has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes.
INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.
how can i resolve this?
View 14 Replies
View Related
Jul 13, 2004
I want in my query to select a different field in case another one is null. in mysql i'd do it like this:
select
a
,if(b is null, c, b)
,d
from
alphabet
how can this be done in sql server?
thanks
View 2 Replies
View Related
Aug 4, 2015
I have a report that uses different datasets based on the year selected by a user.
I have a year_id parameter that sets a report variable named dataset_chosen. I have varified that these are working correctly together.
I have attempted populating table cell data to display from the chosen dataset. As yet to no avail.
How could I display data from the dataset a user selects via the year_id options?
View 4 Replies
View Related
Oct 10, 2005
SQL Server 2000 8.00.760 (SP3)I've been working on a test system and the following UDF worked fine.It runs in the "current" database, and references another database onthe same server called 127-SuperQuote.CREATE FUNCTION fnGetFormattedAddress(@WorkID int)RETURNS varchar(130)ASBEGINDECLARE@Address1 As varchar(50)@ReturnAddress As varchar(130)SELECT@Address1 = [127-SuperQuote].dbo.tblCompany.Address1FROM[Work] INNER JOIN[127-SuperQuote].dbo.tblCompany ON [Work].ClientID =[127-SuperQuote].dbo.tblCompany.CompanyIDWHERE[Work].WorkID = @WorkIDIF @Address1 IS NOT NULLSET @ReturnAddress = @ReturnAddress + @Address1 + CHAR(13)+ CHAR(10)RETURN @ReturnAddressENDSo now the system has gone live and it turns out that the live"SuperQuote" database is on a different server.I've linked the server and changed the function as below, but I get anerror both in QA and when checking Syntax in the UDF builder:The number name 'Zen.SuperQuote.dbo.tblCompany' contains more than themaximum number of prefixes. The maximum is 3.CREATE FUNCTION fnGetFormattedAddress(@WorkID int)RETURNS varchar(130)ASBEGINDECLARE@Address1 As varchar(50)@ReturnAddress As varchar(130)SELECT@Address1 = Zen.SuperQuote.dbo.tblCompany.Address1FROM[Work] INNER JOINZen.SuperQuote.dbo.tblCompany ON [Work].ClientID =Zen.SuperQuote.dbo.tblCompany.CompanyIDWHERE[Work].WorkID = @WorkIDIF @Address1 IS NOT NULLSET @ReturnAddress = @ReturnAddress + @Address1 + CHAR(13)+ CHAR(10)RETURN @ReturnAddressENDHow can I get round this? By the way, I've rather simplified thefunction to ease readability. Also, I haven't posted any DDL because Idon't think that's the problem!ThanksEdward
View 2 Replies
View Related
Aug 26, 2005
I need some help.I am trying to write a query which does the followingSELECT * from table1 where field1=(SELECT distinct field1 FROM table1WHERE field2='2005' or field2='2010')I need all the values from table1 which match any value from field 1from the subquery.Any help is appreciated.thanks
View 4 Replies
View Related
Feb 29, 2008
I would like to query a table for a max value of one field for a distinct combination of two other fields. Let's call these fields RowID, ObjectID, and ObjectType. RowID is an auto-increment field, so for each distinct combination of ObjectID and ObjectType, there will be many values of RowID. To visualize an example:
RowID, ObjectID, ObjectType
1 , 1 , 1
2 , 1 , 2
3 , 1 , 3
4 , 1 , 1
5 , 1 , 2
6 , 1 , 3
Of these rows, I would only want 4, 5, and 6 (max values for distinct combination of ObjectID and ObjectType).
I hope I explained this clearly. I would imagine I'd need to use some form of nested query, but nothing I have tried so far has worked. I am using SQL 2005.
Thanks!
View 1 Replies
View Related
Feb 27, 2008
Hi,
I've got a question!
In a database I have a field interest-target with values like 8,15,115,3 and 18,13,15,6 and 51,6,7,118 etc.
Now I like to select from these fields the value 8. I tried the following:
SELECT * FROM TABLE WHERE FIELD LIKE '%8,%' OR FIELD LIKE '%,8,%' OR FIELD LIKE '%,8%'
I will the also get the value 18 wich I don't like :-(
Any solutions here?
Thanks!
Roel
View 7 Replies
View Related
Feb 15, 2005
I want to select only the descriptions from a table that have a ' in them. Anyone know how to do this?
exp: Select * from SkuMaster where Description like '%'%'
I have tried all kinds of combinations with no luck.
View 2 Replies
View Related
May 31, 2006
Is there a way in SQL Server to query for a character that is in a field? ... I have this old table before there was form validation, and I want to get the addresses out of the column that actually have an @.
I want to do something like this.
SELECT
email
FROM
xyz
WHERE
email contains '@'
AND
email contains '.'
any ideas? Will this work?
Thanks,
-L
View 4 Replies
View Related
Oct 24, 2007
Hi,
Can someone tell me if it is possible to do an SQL insert with a select (to copy specific records) query and specify the value for a specific field to insert in the new records instead of using the value in the field in the select statement.
If so can you provide me with a simple example.
Cheers
Mark :)
View 4 Replies
View Related
May 25, 2008
I've a select SELECT Field1, Field2, Field3 FROM Tablethe problem is that I can have filled Field1 OR Field2 ... I would like to create a new column in the result for see only the filled field if Field1 is = null or = ' ' I want to see Field2 if Field2 is = null or = ' ' I want to see Field1
View 2 Replies
View Related
Jun 13, 2008
How would I go about selecting records where a datetime field is >= 1hr?
View 2 Replies
View Related
Jan 8, 2004
For example i got 1 table T1 and has 1 field that names User_ID
User_ID
-----------
us00019
us00020
us00001
us00002
us00021
us00008
us00005
us00009
us00011
us00010
us00012
us00018
How can i retrive Max User_ID in that field .... i'm using SQL 7.0.. i couldnt do it,anyone got idea. Thankz
View 1 Replies
View Related
Mar 11, 2005
I need a little insight on how to select the same field from the same table, but for different criteria.
here are example tables...
Categories
CATSUBCATNAME
10MainTitle
11SubTitle #1
12SubTitle #2
20Section
21Section #1
DataTable
CATSUBCATINFO
11Detail Information for subtitle #1
12Detail information for subtitle #2
desired result would be:
MainTitle, SubTitle #1, Detail Information for subtitle #1
MainTitle, SubTitle #2, Detail Information for subtitle #2
Select c1.Name, c2.Name, d.info
from DataTable d, Categories c1, Categories c2
where c1.CAT = d.CAT
and c2.CAT = d.CAT
and c2.SUBCAT = d.SUBCAT
View 1 Replies
View Related
May 22, 2006
OK, i'm sure this is an easy one, but can't seem to figure it out. Basically need to search against two database columns (fields) to see if either contain the work "test". Here is an example of the statement but of course dosn't work correctly:
SELECT * FROM tbl_db WHERE skills LIKE 'test' AND title LIKE 'test'
Please note the AND is incorrect.
What i need to accomplish:
if one record contains 'test' in its skills field then SELECT it.
if one record contains 'test' in the title field the SELECT it.
I need to make sure that is 'test' is in both skills and title fields then only display once.
Thanks,
Jake
View 5 Replies
View Related
Oct 27, 2006
here is what i am wanting to do but is it possible to use multi fields with an IN statement?
SELECT *
FROM usmastf
WHERE usm_book, usm_acct IN
(SELECT usac_book, usac_acct
FROM uscommf
WHERE usac_code = 'O/W')
View 14 Replies
View Related
Dec 15, 2006
hello !
for MS SQL 2000
how can I get something like
SELECT name from users WHERE Count(name) > 1
i want to return only the rows where name appears more than 1 time
thank you
View 12 Replies
View Related
Apr 18, 2008
Dear all,
I have problem in the following SQL. Please help. The problem in the inner select statement for the group by clause. Please help
select company.billtocompany,
(select quartername from quarter where a.orderdate between
convert(datetime, quarter.startdate) and convert(datetime, quarter.enddate)+1) quartername,
sum( a.qty) orderedqty
from a, company, countrycode
where a.billtocompany=company.compcode and
a.countrycode=countrycode.countrycode and
a.billtocompany='111111' and
a.orderdate between convert(datetime, '2008/01/01') and convert(datetime, '2008/10/01')
group by company.billtocompany, countrycode.countryname, quartername
View 1 Replies
View Related
Jun 9, 2008
Hi there,
Need help..
I have a table name Changekitlist and have fields name CKNumber,ck_ID,ck_Name, etc.. Now, I plan to see each CKNumber one at a time so that I can put it in an array which I'll use in VB6 program.
However, If I use the command: "SELECT CKNumber from Changekitlist"
it displays all the CKNumber list that I have (CK001 to CK500) and I cannot store it in an array properly because it display all CKNumber at once and store all the 500 CKNumbers at once in the array.
What will I do so I can display one CK Number at a time, store in an array and loop so that I'll get the next CKNumber and store it again.. Need your advice please. Thanks!
View 12 Replies
View Related
May 15, 2014
I need to check if the latest record in a table for the current client, based on EFFECTIVE_DATE, has a value of 'Y' in the STATUS field. Sample records for client #19:
CLIENTEFFECTIVE_DATESTATUS
------ -------------- ------
19 08/13/2010Y
1901/10/2013N
1902/03/2014Y
If I run:
SELECT CLIENT, max(EFFECTIVE_DATE), STATUS
FROM FTABLE GROUP BY CLIENT, STATUS
I get the two latest records since STATUS is different:
CLIENTEFFECTIVE_DATESTATUS
------ -------------- ------
1901/10/2013N
1902/03/2014Y
I wrote the following and it works, but it may be causing 'Insufficient Disk Space' error from the database. Is there any way to write this query without temp table?
DECLARE @TEMPMAX
TABLE (CLIENT INT, EDATE DATE)
INSERT INTO @TEMPMAX (CLIENT, EDATE)
SELECT CLIENT_NO, max(EFFECTIVE_DATE)
[Code] ....
CLIENTEFFECTIVE_DATESTATUS
------ -------------- ------
1902/03/2014Y
View 3 Replies
View Related
Jul 26, 2007
Hi,
I was wondering if anyone knows a command that will select all fields in a table except one?
Thanks!
Lee
View 6 Replies
View Related
Sep 21, 2007
I'm sure it is possible to select all records where the length of a given field equals a certain value, but I don't know how to do it?
Can somebody tell me how?
Hans
View 4 Replies
View Related
