Retrieve All Rows With Largest Value In A Particular Column?
Jul 18, 2012
I am trying to retrieve all rows with the largest value in a particular column. The largest value could return many rows for a particular users. Here is what I have thus far.
SELECT DISTINCT
ID, NAME, FOP, ACCT, CTNUM, ENDDATE, DEBIT, CREDIT, TRANSACTION_DATE, EXPORTED, CALENDAR_YEAR, FISCAL_YEAR, PAYROLL_IDENTIFIER,
PAYROLL_NUMBER, [EARN-SEQNO], EVENT_SEQUENCE_NUMBER
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY ID, ACCT, PAYROLL_NUMBER,EVENT_SEQUENCE_NUMBER
ORDER BY EVENT_SEQUENCE_NUMBER DESC) AS RN
FROM PAYROLLYEAREND ) s
WHERE RN = 1 AND ID = '16443' AND PAYROLL_NUMBER ='7'
In the above example, the EVENT_SEQUENCE_NUMBER is populated with values from 0 to 12. Could vary per user and PAYROLL_NUMBER. The query above returns 48 rows. However, all I want are the rows where EVENT_SEQUENCE_NUMBER is equal to the highest, which is in the above example is 12. The result would be 29 rows. The where clause is not part of overall query. Just isolating on one ID.
Example data CA1000 CA10001 CA10002 CA10003 CA11597 CA11603 CA1001 CA998 CA999 As you can see, CA11603 is the largest number in this list. When I try the follow sql code, SELECT MAX([MyCode]) FROM [MyTable] WHERE (SUBSTRING([MyCode], 1, 2) = 'CA') The largest number comes back as CA997 When I try MAX([MyCode]) FROM [MyTable] WHERE [MyCode] LIKE 'CA%' The largest number comes back as CA997
SELECT TOP 1 (SchoolMasterCode) FROM SchoolMaster WHERE (SUBSTRING(SchoolMasterCode, 1, 2) = 'CA') ORDER BY Schoolmastercode The largest comes back as CA10001 When I try.... SELECT TOP 1 (SchoolMasterCode) FROM SchoolMaster WHERE (SUBSTRING(SchoolMasterCode, 1, 2) = 'CA') The largest comes back as CA1278 What am I doing wrong?
Hi I have a table with a column 'value' as nvarchar I want to select some rows from this table that the value of 'value' can be converted to decimal ... it's possible that value of 'Value' contains some other characters other than digits .
I have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.
Can any one you help me in retrieving the rows which immediately after a ID 9. i.e, My condition is to retrieve the Record which immediately follows the event 9. The retrieved record event should be in (24,25,26)
My out put should have --------------------------------------------- MatchID SegmentID Event Player1 Player1ClubID --------------------------------------------- 1 0 24 1 89 1 0 25 1 88 ---------------------------------------------
How to get this, Can any one help me.
Thanks Ganesh
Solutions are easy. Understanding the problem, now, that's the hard part
I have a table MyTable that contains two fields MyData and CreationTime. I would like to write a query SELECT MyData FROM MyTable WHERE CreationTime < less than one week from now Does anyone know the real syntax for that?
I've just deleted some rows by error in one of my tables. I'd like to find out a quick way to retrieve them. I'm using MSSQL server 2000, version 8.00.194
I have a database table where upon inserting a new record, I get 1 row(s) affected twice. But when deleting the record, I only get one instance of that message. I'm not really an expert, neither have I designed the database, but how can I retrieve the actual affected rows or determine what table does the other row pertain to?
Hi, I am new at SQL hopefully this would be a rather easy question for you guys to help me out with. I have a table called PRODUCT with the following fields: a. Product Name b. Product Dept. c. Purchase Date.
I would like to run a query to obtain all rows tha has more than one purchases on any particular day.
i got a error while retrieving more than 100000 rows (records) from a table .. can any one tell me what is the maximum number of rows retrieve from a database to reports... and how can i overcome this issue...
i have a table that has 9 columns that belongs to other tables. i need to retrieve one column from this table. In order to do this, do i have to join all the other tables that has similar columns?
Name x y z z1 A 1 2 3 3 B 0 2 0 3 C 1 0 3 3 if i pass parameter as @param=1, only column of x should be displayed, if i send parameter as @param=2 only y column should be displayed, if @param=3 is parameter then Z column And Z1 column should be displayed. plz help to create sql query.
Is there an easier way to determine which tables in a db are the largest other than running sp_spaceused for each table? Interested mainly in physical size.
Hi, all.. I want to know the query to retrieve Column information that we can see from table Design view of Enterprise manager, such as Column name, Pk or not, FK table, Data Type, Null or not, Description(Specially Descrition).
I have search for and see how to put the columns for the last, first mi together (Name_Last + ', ' + Name_First + ' ' + Name_MI) as Name But how can I test the value of the Name_Type field to determine how the Name column looks if Name_Type = 1 then Name = (Name_Last + ', ' + Name_First + ' ' + Name_MI) else Name = Name_Last
The Name_Type represents Individual versus an Entity 0,ABC Pipeline,, 1,Williams, John,A
What would be the most efficient method of constructing a sql statement to retrieve certain record into (this gets tricky) rows of a table with 3 columns date.desc.
I have two tables something like this : Table : Account AccountID UserName Password
Table : Address AddressID AccountID Line1 Line2 State
My NHibernate mapping file looks like this : For Table Account <id name="ID" type="long" column="AccountID" access="property"> <generator class="identity"/> </id>
My Problem is it does entry in the Account table perfectly, but when it comes to do entry in Address Table it says AccountID can't be null, I'm wondering shouldn't it pick it up from the Account table as I'm passing in that object.
For the Workaround I was thinking maybe I can get the identity of the last entry saved and somehow save it to the other table myself.
I have a table with an Identity column. Is there a way to get the automatically generated primary key after I insert something to the table? If so, what is the syntax?
Is there any way to retrieve partial contents of a database column? For example, say a column holds 5,000 characters, but I only wish to retrieve the first 50. Thanks
I want to just retrieve the numbers left of the space in this address column what can I use? The Address column is Varchar.
Address: 6501 Red Hook Place #201 32 Orchard St 1 Hardy Rd ste 210 379 E Center St. 170 Bridge St 399 Interpace Pkwy 1800 Valley Rd 2210 Harding Hwy 41 Maple Ct
Expected Results:
Address 6501 32 1 379 170 399 1800 2210 41
I tried using this:
Left([ADDRESS], Charindex(' ',[ADDRESS] - 1)) as 'Address'
But i received an error:
Conversion failed when converting the nvarchar value '6501 Red Hook Place #201' to data type int.
We have a SSIS project where we load lot of image files using an OLE DB Source component and put it across the database as varbinary into OLEDB Destination component.
Things were fine until now where am getting an error like this. alongside the log also reads like,
There is not enough space on the disk. Long data was retrieved and can't be added to the data flow task buffer.
Is this method of loading the files using an OleDb Souce not the efficient one ? If not is there a way I can get this done. Comments are highly appreciated!!
