How do i select first name of the lets say 6th highest salary of an employee?
is it use Select TOP statement?
p/s: I just want the 6th largest fname not the top 6. so it will only return one value.
Thank you in advance for your reply.
I want to find 3rd largest value from the table. i.e
Table Data:
Col1 Col2 col3
1 Name 1000
2 Name 2000
3 Name 1500
4 Name 500
so i want sql statement that gives me output....
Col1 Col2 col3
2 Name 2000
Thanks
-Binoy
just needed to know the datatype that had the largest amount of characters that could be allowed within the field. thanx.
View 1 Replies View RelatedHi,
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.
Thanks
Hi,
I want to know the top 20 largest tables in my database, by the size NOT by the number of rows. Can anyone give me a query which does it for me?
Thanks in advance,
David
Hey
Its easy to get the MAX or MIN from a table,
but what if you want to get the 2nd largest ? or 2nd smallest ?
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.
Greetings
I was tring to cast an xml document using varchar(max) but it chops it off at excatly 9643 when I do SELECT @varcharProductionBody = CAST(@xmlProductionBody AS VARCHAR(MAX))
PRINT LEN(@varcharProductionBody)
So what is the largest data type for string and/or characters in SQL 2005? I have looked high and low but am unable to find one.
Thanks
I would like to exec a select statement in VB/C++ to return first 100 records? What is the SQL statement should be?
Thanks,
Sam
i have table in sql and every month i am appending new data in this table, but i want to sort data Culumn Name "account_no' from smallest to largest, and whenever i append new data to this table it auto sort every time,
View 1 Replies View RelatedExample 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?
I would like to determine the largest numeric datatype scale of a column.
Decimal Largest Scale Solution found here.
Sample Data:
2222.0002430
2222.0000245
2222.0023455
I usually go for the largest datafile and then query in-there...But now I need to automate it for several instances... I need to be able with one script quickly retrieve the top 5 largest tables for the entire instance,not by database...
View 3 Replies View RelatedHi
Here is my example: I have the following table
ID Name Phone
--- ---- -----
1 John 1234567
1 John H.
2 Dave 9876543
2 Dave Smith
I want to select only one record of each ID, the output should be
ID Name Phone
--- ---- -----
1 John 1234567
2 Dave 9876543
It doesn't matter which record to select, but I need just a single complete record. I can't use select distinct ID because it will not show the other fieldnames.
Thanks
I have a sql query that looks for values in a few different databases....
is there a way to select the last record in the table b/c I am pulling the hours worked on jobs and one person may have 3 job titles but i want it to show the balance of hours under the most recent job... Instead all the jobs are showing the same value even though only one of them had those hours under it. I think a way to get around this is to select the last value b/c that is under the job title most recently worked and they are in order by timesheets which can be ordered by date...any ideas?
I have view with Patients name and Appointment table where I save those patients. How to create store procedure if I would like to select one patient record based on Patient_Id value?
View 3 Replies View RelatedID A B C AVG
------------------------
1 08 09 10 -
------------------------
2 10 25 26 -
------------------------
3 09 15 16 -
------------------------
I want to calculate the average of the larges two number from the column A,B & C for particular identity and store that average in the AVG column....
Hai frendz,
I am having a table named Employee(int EID, float Salary)...
Now I want to select the highest salary in the table and the query is-
"select top 1 EID, Salary from Employee ORDER BY Salary DESC"
Now I need to write a query which selects the second highest salary.
So how to achieve this?..
thanx
hi
I want to lock the record on select query so that no other user can update this record, is it possible and i want to unlock record when use stop view this record.
i have two tables A and B. relation is one to many for A. i want to select from A only if there are more than two records of A in table B and also checking some condition in table B. if the question's not very clear please let me know.
View 1 Replies View Relatedhow to select record from the table where the data between a range. example between 2/16/2005 and 12/16/2005. the data record in the table formated like this ( 2/16/2005 11:44:38 PM). help me with some sql code, thanks
View 2 Replies View RelatedHi,
I need to select first record from a table.
This can be comfortably achieved by usibg set rowcount 1.
I need to do this with out using rowcount.
This is urgent.
Thanks
Krishna
Hi!
I need to select a spesific record using the recordkey and then select the previous and the next record as well. (which leaves me with a recordset containing three records)
Is this possible?
hi friends,
I am having a table with student marks in that i need to select a student who is the 5 th rank based on total marks obtained by the student.how can write sql for that.
name, totmarks
-----------------
kumar 145
ravi 300
jude 189
geeetha 320
rajesh 251
guru 190
ramesh 99
----------------------
i am having records like this.pl help me. thank u
regards
raj
Hello, everyone:
I have two tables like:
Table A
IDCodeANumA
1D1289
2C4300
3C9409
Table B
IDCOdeBNumB
1D3266
2C4300
3C7101
How to find out the records that is in Table A not in Table B, and in Table B not in Table A? That means C4/300 should not be selected.
Thanks
ZYT
The developers of frond end ask to select only one row from one table, but many times, such as 10. That means the output has 10 rows and same.
Any suggestion will be appreciated.
ZYT
Every hour we capture some values from our factory (position of pumps, valves, ...) in our sql server 2000 db.
Normally 1 record is added to the db.
00:00:00
01:00:00
02:00:00
...
21:00:00
22:00:00
23:00:00
All of these values are displayed in an Excel sheet. One sheet contains all the data from one month.
I noticed a problem last week when 2 records were added: first one at 19:00:00 and the second one at 19:00:01
We only want to keep the most recent record (19:00:01) in a situation like this but I can't seem to work out an SQL-statement :o
This is what we have know. It used to work fine untill we had 2 record being added instead of one.
SELECT TimeOfCapture, Value1, Value2, Value3
FROM FactoryTable
WHERE (MONTH(TimeOfCapture) = MONTH(GETDATE())) AND (YEAR(TimeOfCapture) = YEAR(GETDATE()))
Hi everyone,
I am having a header table and details table and I want to display first 2 records against each header from details, whatever number of records are there in details against each header.
Example :
=======
Details table is as follows
HeaderID DetailsID
1 1
1 2
1 3
2 4
3 5
3 6
3 7
3 8
output should be:
TransDate SupplierName HeaderID DetailsID
1/1/2000 abc 1 1
1/1/2000 dsds 1 2
2/3/2003 fgd 2 4
2/4/2005 sdsd 3 5
1/1/2006 fgfdg 3 6
I am using the following query
SELECT H.TransDate, H.SupplierName, D.DetailsID FROM
Header H, Details D
WHERE H.HeaderID = D.DetailsID
AND D.DetailsID IN (SELECT TOP 2 DetailsID FROM Details WHERE HeaderID = H.HeaderID)
As I am dealing with very huge data it is taking very long time to execute.
Is there any better way to accomplish the task?
Thanks.
Riyaz
im trying to get the earliest record. data is below
note_id/ doc_received_date/ bankruptcy_date/ sp_recorded_date
2332/ 20090106<---- / 20081219/ 20090106
2332/ 20090323/ 20081219/ 20090323
2332/ 20090413/ 20081219/ 20090413
2332/ 20090507/ 20081219/ 20090507
because the bankruptcy_date date are all equal i would need to pull one record with the earliest date from the doc_received_date. The date with the arrow is the record i want. So basically i would like to show this result
note_id/ doc_received_date/ bankruptcy_date/ sp_recorded_date
2332 20090106 20081219 20090106
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
I am wondering how to select the Max date within a table where a timestamp is within 2 second of the other
IDTimestamp
1NULL
209/17/2012 11:04:32
309/17/2012 11:05:44
409/17/2012 11:05:45
So I need a query to return
IDTimestamp
409/17/2012 11:05:45
as 11:05:45 is the max of 09/17/2012 11:05:44 and 09/17/2012 11:05:45 and they are within 2 seconds of eachother.
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
Hi,
I want to construct a query that would give the most recent record. Table data looks like -
F1F2F3 F4
20016395074/7/2004 15:40:55
23516395074/6/2004 9:31:54
All columns are strings as the data is obtained from text file. Column F3 can have same or different dates. In the above data select 1st record as it is the most recent.
If data is like -
F1F2F3 F4
20016395074/7/2004 15:40:55
23516395074/7/2004 19:31:54
Select the 2nd record.
How do I construct the SQL query?
Let me know.