Solved

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

Posted on 2014-12-30
5
130 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
Comment Utility
I should mention, i am searching in vb express 2010 and looking into SQL server 2012
0
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
Comment Utility
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
Comment Utility
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 62

Expert Comment

by:Fernando Soto
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now