How to find the nearest value in the database

Question

How to look for the nearest value in the database, for example the data in the database for the value of:

Data that will be tested is -100, how the system can issue the results of the test -100 is the character A.

Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
    Try
        Dim persen As String
        Dim nilai_banding, hasil As String
        Dim cek = TextBox1.Text
        Dim reng_atas = cek + 15
        Dim reng_bawah = cek - 15
        Dim per = " %"
        Dim kuadrat = "^2"
        Dim pixeluji = TextBox2.Text
        Dim pixelsampel = TextBox1.Text
        'Dim C = TextBox3.Text

        'If C > 315 Then
        'MessageBox.Show("Jumlah Pixel Terlalu Besar Untuk Di Verifikasi", "Informasi", MessageBoxButtons.OK, MessageBoxIcon.Information)
        'Else

        'conn.Open()
        Dim mDA As New MySql.Data.MySqlClient.MySqlDataAdapter("SELECT citra_karakter From karakter_plat WHERE pixel_putih='" & TextBox1.Text & "' or pixel_putih >='" & reng_atas & "' and pixel_putih >='" & reng_bawah & "'", conn)
        '("SELECT Nama,Tanda_Tangan,Nilai_Hitam,Jumlah_Pixel,Keterangan From tanda_tangan WHERE nama LIKE  '%" & TextBox7.Text & "%'", conn)

        Dim dt As New DataTable
        mDA.Fill(dt)
        'DataGridView1.DataSource = DataGridViewAutoSizeColumnMode.AllCells
        'DataGridView1.DataSource = dt

        CMD = New MySql.Data.MySqlClient.MySqlCommand("SELECT nama_huruf,pixel_putih,pixel_hitam,jumlah_pixel,biner_karakter, citra_karakter FROM karakter_plat WHERE pixel_putih='" & TextBox1.Text & "' or pixel_putih<='" & reng_atas & "' and pixel_putih>='" & reng_bawah & "'", conn)
        RD = CMD.ExecuteReader()
        RD.Read()

        If RD.HasRows Then

            Label1.Text = RD.Item(0)
            TextBox2.Text = RD.Item(1)
            'Dim foto As Byte() = RD.Item(5)

        End If

Syntax above produces output -100 = N

I want -100 = A.

Your question is not very clear. please describe your database tables and also use the appropriate tag for your rdbms. — Zohar Peled, Jun 15 '15 at 11:44

shA.t · Answer 1 · 2015-06-16T04:44:12.343

My answer is based to find nearest values of a field to a specific field by using a query:

SELECT *
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (ORDER BY ABS(@specificValue - valueField)) AS seq
    FROM
        aTable) t
WHERE
    (seq <= 1); --if you want to have n nearest values change `1` to `n`

Using this type of query (for example) will give you -99 instead of -102 for @specificValue = -100.

As an alternative query (in MySQL):

SELECT *
FROM aTable
ORDER BY
    ABS(-100 - pixel_putih)
LIMIT 1;

How to find the nearest value in the database

1 Answers1