How do I get the row number in a SELECT
Select ???? as Row, Name, Stars From Member
Row Name Stars
---------------------------------------
1 Enigma ****
2 BreittKaiser *****
3 BlindMan *****
Got this query and I need the following result;
declare @NumberToCompareTo int
set @NumberToCompareTo = 8
declare @table table
(
number int
)
insert into @table
select 4
[Code] ....
The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5
The select command below will output one patient’s information in 1 row:
Patient id
Last name
First name
Address 1
OP Coverage Plan 1
OP Policy # 1
OP Coverage Plan 2
[code]...
This works great if there is at least one OP coverage. There are 3 tables in which to get information which are the patient table, the coverage table, and the coverage history table. The coverage table links to the patient table via pat_id and it tells me the patient's coverage plan and in which priority to bill. The coverage history table links to the patient and coverage table via patient id and coverage plan and it gives me the effective date.
select src.pat_id, lname, fname, addr1,
max(case when rn = 1 then src.coverage_plan_ end) as OP_Coverage1,
max(case when rn = 1 then src.policy_id end) as OP_Policy1,
code]...
I have a problem where I essentially want to find all the B_ID numbers that do not have any ones or twos next to them in the whole table (see table below).
I come up with this query, however it is wrong, because certain B_ID do not have a 1 or 2 on some lines, but then they do on others :(.
SELECT B_ID
FROM NUMBER
WHERE NUM != 1
AND NUM != 2;
SELECT * FROM NUMBER;
+------+------+------+
| A_ID | B_ID | NUM |
+------+------+------+
| 101 | 101 | 1 |
| 101 | 102 | 2 |
| 101 | 201 | 3 |
| 101 | 301 | 4 |
| 102 | 201 | 2 |
| 103 | 102 | 3 |
| 201 | 101 | 1 |
| 301 | 301 | 2 |
| 401 | 102 | 7 |
| 501 | 102 | 1 |
| 501 | 301 | 3 |
+------+------+------+
11 rows in set (0.00 sec)
Hello all, I am using SQL 2000 SP4.
I have a table with a studentID column. Is there any way to select a single
row of data from that column by referencing the row number?
ex. select studentID from Table where row = 2
The above example obviously does not work, but is there a way to do
something similar? I want to go down the table sequentially and knowing any of the data in the other columns does not help me with that at all.
Any help is appreciated, thanks
Hi,
I have a fairly basic query I want to make but I'm struggling on figuring out how to do it. Let's say I have some fields (e.g. Value1, Value2, Value3, etc). I simply want to do a Select statement that returns the highest value among those fields for every row in my db.
At first I thought of the built in Max function until I realized that is for within a column only.
Any help would be appreciated, thank you.
I swear I have done this before and can't remember how.
I want to select columns in a SELECT statement by their number instead of name.
I have looked everywhere for the syntax.
Also, is there a function to 'count' the number of columns in a table?
Thanks.
Hi all,
Hopefully you can assist this pleb (me) with some (hopefully) basic scripting.
I have a table which has bucket loads of rows in it (funnily enough)
Field1 & Field2
Field1 is repeated numerous times (but also has differing values) through the table
Field2 is a numeric value which is assigned to the Field1
What I need to do is pull back all the unique Field1's but only those with the highest value in Field2
Example:
Field1 Field2
blah 100
blah 400
blah1 12
blah1 9
blah1 2
blah 350
I need the return to be basically:
Field1 Field2
blah 400
blah1 12
I am sure that this has to be straight forward - however my brain just isn't connected the right way at the moment....
Thanks in advance for any assistance on this.
Troy
I want to select with column order without field name .
Is it possible ?
SQL2000 db
...
I want to select a field by its order in table, but without naming it to have a constant name of it, like :
select (column01) as L1 from myTable
Hi all!I am looking for a way to select only numbers OR only the first word from a field.EG:1 cartoon => 12 cartoons => 2200 cartoons => 2007 Allo => 71 Poil 2 Pirates 3 Bizounes => 125Kilos2 => 25Where the values after the => is what I want as a result.Anybody knows how to do that ?
View 7 Replies View RelatedI have a price field that when I query it, it returns the price as"5.0000". What function can I use to return the price as "5.00"?THANKS
View 4 Replies View RelatedA table has a column of int type. I need to select a fixed number of rows for each value. For example, if data in that column (c) are 5, 6, 7, and the number I want to select is 2, then I need 2 rows from c=5, 2 from c=6, and 2 from c=7. How to write that query? Any idea?
Thanks.
Hi
I'm trying to convert and format integer values in a SQL Server select statement to a string representation of the number formated with ,'s (1000000 becomes 1,000,000 for example).
I've been looking at CAST and CONVERT and think the answers there somewhere. I just don'tseem to be able to work it out.
Anyone out there able to help me please?
Thanks,Keith.
I am working on a multi-page datagrid that pulls data from a database. The issue I am running into is the SQL select query. What I have is a table to 55 items. What I need to do is grab the first 35 of those items and bind the resulting DataReader to the grid, print the page, then grab the remaining 20 items, bind to the grid and print the page.
I can use "SELECT TOP 35 FROM Table" to get the first 35 items, but I don't know how to get the remaining 20 items. Is there a way to say something like "SELECT TOP 20 FROM Table" but specify only the rows that begin after row 35?
I tried doing this with an ArrayList but couldn't bind it to the DataGrid.
Any suggestions?
Hello, I have a field of type the Var, the stored data makes about 700 characters.
During a select through the query analyser, it tronque in about 200 or 300 characters.
How to explain it?
thank in advance
Pascal
Here is the example
state
-----
NY
NJ
CA
NY
NC
NJ
CA
IL
CA
Can we generate a result like this with select statement
state No
----- ---
CA 1
CA 2
CA 3
NJ 1
NJ 2
NY 1
NY 2
IL 1
NC 1
hey all,
I am writing a sproc:
select @strCourseNameRegFor = sal.CourseName , @strSectionNoRegFor = sal.SectionNo,
@dteStartDateRegFor = sal.StartDate, @dteEndDateRegFor = sal.EndDate,
@dteStartTimeRegFor = sal.StartTime, @dteEndTimeRegFor = sal.EndTime,
@strDaysOfWeekRegFor = sal.DaysOfWeek
from lars.dbo.tblSalesCourse as sal, lars.dbo.tblCourseCatalog as cat
where sal.SchoolYr = @intRegForYear and rtrim(sal.SchoolTerm) = rtrim(@strRegForTerm) and upper(rtrim(sal.CourseName)) = upper(rtrim(@strCourseNamePrev))
and cat.NewStuAllowed = 0 and sal.CourseName = cat.CourseName and sal.Cancelled <> 1 and cat.SchoolYr = sal.SchoolYr
and sal.MaxNoStudents > sal.CurrNoStudents
I want to check if the select returned an empty set or not. I cannot use @@rowcount because i am assigning the values to the local vars. I tried
if @strCourseNameRegFor is null
begin
set @err = 'No courses';
end
but for some reason even if there are any records in the set, the if condition is getting satisfied. Can anyone help?
I am assigning temporary Ids in my table, and right now I am assigning new ones via find the MAX(Temp_Id), stripping out the number and adding one.
Since these are temporary I would like to be able to reuse older ones when they are no longer in the table.
For example I have:
ID0001
ID0002
ID0003
ID0006
My code right now will add ID0007. I would like to re-use ID0004 and ID0005 since they're no longer in use. How do I go about do this?
I'm trying to select records that occur more than once. I'm trying to base this on the email column. So basically I want the query to look something like this:
select * from table where emailaddress count > 1
Can someone provide me with the correct syntax? :)
Dear Friends!
i have one table namely details
empid type qty
emp1 bucket 5
emp2 bucket 5
emp4 Book 5
emp5 Lux 5
.. .. ..
.. .. ..
nenp n n
Every end of the day i need select total no of type and qty.i have milions of records in the table
ex bucket 100
book 300
How it is possible
Regards
Umapathy
In other SQL programs I have seen the use of Limit to tell it how many rows to return.
Is there an equivalent in MS-SQL that will let me do a quick Select clause and tell it how many rows to return.
In other words if I just wanted to see the first 10 rows what would I add to Select * from tableA
Thanks!
I have a table with entries tied to a membership database. The problemis that I want to select a limit of sixteen entries per member, perday, where some members have 16+ entries per day.I have this so far ( which I've simplified for this post)SELECT dbo.members.firstname, dbo.members.lastname,dbo.entries.gameDayFROM dbo.members INNER JOINdbo.entries ON dbo.members.memberID =dbo.entries.memberIDIf it's day 5, each member should have 80 total.How can I change this to select only 16 entries for each member, makingsure it's 16 per day based on dbo.entries.gameDay?Thanks for your help.
View 2 Replies View RelatedDear GroupI wonder whether you can give me a syntax example for a SQL Statement.Lets assume I've a table containing three columns ContactID (Primary Key),Firstname and Lastname.I would like to write a stored procedure which returns me the first tenrecords and increments an outside variable each time it runs.E.g If I run it the first time I pass the variable as 0 and it will returnme the first ten records and returns the variable value 1.When run a second time, I will pass the variable as 1 and it will return merecords 11-20 and sets the variable to 2 and so on...The difficult thing is how to tell to return me records 11-20. I can't usethe ContactID as someone might have deleted a row and e.g. ContactID 18 ismissing. In this case I only would get 9 rows returned. It always should beten.Thanks very much for your time and efforts!Kind Regards,Martin"There are 10 types of people in this world: Those that understand binaryarithmetic, and those that don't."
View 3 Replies View RelatedI 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!
Hi,
Im using a select statement to query out the data from a table.
ex:- select * from tblperson
Now this select statement is returning me 500 rows
I want to see only the records between 100 - 200 out of these 500 rows..
Anyone help
I have a SELECT statement with multiple JOINs. I want to number the rows returned by this SELECT statement. What's the easiest way to do this?
My current SELECT statement returns this:
ProjectId -- TaskId -- TaskName
123 - 111 -- Do something
123 - 222 -- Do something else
123 - 333 -- Do one more thing
I want to return this:
ProjectId -- TaskId -- TaskName -- Sequence
123 - 111 -- Do something -- 1
123 - 222 -- Do something else -- 2
123 - 333 -- Do one more thing -- 3
Can anyone just point me in the right direction. All I want to do is add some T-SQL to an existing stored procedure to return the number of rows selected into a return value.Does anyone know how to do this?
View 4 Replies View RelatedI try this sql query:
Code:
SELECT
[NAMES], [NUMBER]
FROM
[CV].[dbo].[T40]
WHERE
[NUMBER] = '44644'
GROUP BY
[NAMES], [NUMBER];
The output is:
Code:
NAMESNUMBER
BENCORE S.R.L.44644
BENCORES.R.L. 44644
I need instead this other output:
Code:
NAMESNUMBER
BENCORE S.R.L.44644
I have a sql line that selects records:
select * from prodstatistics order by shift_started desc
I do not need to read the entire table and want to limit the read to the first 500 records. How would I embedd that in my current statement?
Thank you.
There is column mobile_number in my table
I have to replace with star(*) to the middle six character from contact number of 10 digit.
For example
The Contact number is 9334459875
I have to display it as follows
93******75 first two digit and last two only.
I have a table with number and varchar columns. The last insert statement has 1 inserted.
The select statement should retrieve
a b
1 1
CREATE TABLE [dbo].[test1](
[a] [int] NULL,
[b] [varchar](10) NULL
) ON [PRIMARY]
insert into test1 values (1,'a')
insert into test1 values (2,'b')
insert into test1 values (4,'d')
insert into test1 values (12,'x')
insert into test1 values (15,NULL)
insert into test1 values (1,1)
I have the following table:
Policy Sequence LossDate Description Amt
123 1 9-1-2012 property 100
123 2 10-1-2012 property 200
345 1 9-1-2012 property 100
345 2 10-1-2012 property 100
678 1 9-1-2012 property 100
I want to select the data as follows:
Policy LossDate1 Description1 Amt1 LossDate2 Description2 Amt2
123 9-1-2012 property 100 10-1-2012 property 200
345 9-1-2012 property 100 10-1-2012 property 100
678 9-1-2012 property 100 NULL NULL NULL
Is there a way to do this using T-SQL?
The max sequence number can vary, so far the max has been 12, but may be higher in the future.
A simple Query to select alternate rows from a table ?
View 9 Replies View Related