Solved

Find a speciifc row number of an SQL table by search data within the table

Posted on 2014-12-30
5
135 Views
Last Modified: 2015-01-02
I need to identify row numbers by data in column "Hour"

Hour     Row Number
0                     0
1                     1
12                   2
13                   3
14                   4

For example, look up the row number of hour 12 should return 3, the row numbers are in numeric order starting at 0, the hours could be random on any row.  I can not use ID numbers to solve this as the ID numbers could be anything.

Thanks for the help.
0
Comment
Question by:SweetingA
5 Comments
 

Author Comment

by:SweetingA
ID: 40524874
I should mention, i am searching in vb express 2010 and looking into SQL server 2012
0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 40524910
Hi SweetingA;

Assuming that Hours is a Integer data type in the database and it only appears once in the database this should return the [Row Number] column.

Dim hour As Integer = 12
'' TableNameHere needs to be replaced with the TableName from the database
Dim sql As String = "SELECT [Row Number] FROM TableNameHere WHERE Hours = + & hour

Open in new window

0
 
LVL 3

Expert Comment

by:Ali HND
ID: 40524923
Dim count As Integer
con.Open()
query = "SELECT COUNT(Hour) AS Hourcount FROM Yourtable WHERE Hour = 12 "

cmd = New SqlCommand(query, con)

count = cmd.ExecuteScalar 
MsgBox(count)

con.Close()

Open in new window

0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40524927
Reposting code there was a " missing in my original post.

Dim hour As Integer = 12
'' TableNameHere needs to be replaced with the TableName from the database
Dim sql As String = "SELECT [Row Number] FROM TableNameHere WHERE Hours = " + & hour

Open in new window

0
 

Expert Comment

by:NonComposMentis
ID: 40524951
Absent a built in function to give you the row number (such as is provided in SQL Server and Oracle), what I would probably do is create a temporary table that looks like the explanatory table you started out with.  In T-SQL that would be

DECLARE @TempHours TABLE
(Hour INT, "Row Number" INT IDENTITY(1,1)); //i.e. start at 1 and increment by 1 automatically

The keywords DECLARE and IDENTITY are T-SQL keywords and will probably be different in your dialect.  I would start by searching for "auto increment".

Then dump all your hours into the Hour column

INSERT INTO @TempHours (Hour)
SELECT Hour FROM YourTableNameHere; // to use Fernando's table name notation

Assuming the selection happens in row order - not guaranteed, but what else can you assume(?) - you will have almost the table you presented in your question, and can query it from there.  The difference is that the row numbers start at 1 instead of 0.  You can make it 0 if you want, but 1 base row numbers seem to be what you are looking for.

For example,

SELECT [Row Number] FROM @TempHours
WHERE Hour = 12;
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question